Get Data From Website to Access Form

Insert Data Into Access Database From HTML Page

Internet API:  It is sometimes useful to be able to send a request out to a website to retrieve live and timely data.  Stock quotes are a good example for this functionality.

Here we have a demo which retrieves data from Yahoo Finance website and display the data on an Access form.  You can use the same code to bring timely data to a report or within a query.

Here is an image of the objects involved:

The table is simply a list of stock symbols:M_StocksStock_SymbolAPPLIBBQQQXOM

Here is the function which does all the work:

Public Function quote(strticker As String)

Dim strurl As String, strcsv As String, strRows() As String, strColumns() As String

strurl = “http://download.finance.yahoo.com/d/quotes.csv?s=” & strticker & “&f=l1&ignore=.csv”

Set http = CreateObject(“MSXML2.XMLHTTP”)
http.Open “GET”, strurl, False
http.send
strcsv = http.responsetext

quote = Nz(CCur(strcsv), 0)
Set http = Nothing
End Function

This is a screen shot of the form in design view:

Note that the Stock Quote field passes the Stock_Symbol to a visual basic function.  The function returns the current value of the stock quote.  As you type in new symbols on the form the current price will be retrieved.  You can use the ‘Refresh All’ menu choice at the top of the form to get a fresh quote.

Feel free to download our Access database example for using a website API  to retrieve data into Microsoft Access.

And finally an image of the form running with all the stock symbols with current value.  Hopefully when I look at this in two years all the values will have doubled so that I can retire.

Microsoft Office Forms:
MS Access 2003
Access 2007
Access 2010
Access 2013

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016