Microsoft w

Visual Basic Stock Quote

Visual Basic Tutorials:

Stock Quote VB Yahoo API

Stock quote downloads in to Microsoft Access took a surprisingly long time to figure out. I thought it would be simple to create the VBA code to talk to Yahoo Finance and load stock quotes into my Access tables. Most of the examples where for PHP, C++ or Excel and where difficult to get running just right.

Did you know you don't need to move to SQL/Server to run and manage your Access database on the net. There are many alternative solutions. Among them are Amazon's AWS cloud services and various hosting services in nearly every state.

Around the last week of April, 2015 the Yahoo Finance API stopped working . After much research it seems the broken API was cause by a slight change in the options of the command line. The old broken API interface line starts like this:

The new fixed line looks like this:

In addition to current share price information you may download a long list of other stock attributes such as yield, last trade, day's low, day's high and more. This API will work for stocks, bonds, mutual funds, ETFs and most any equity with a valid symbol. Below is the VBA code programmed to download Yahoo Finance stock quotes into my database table.

How To Get Stock Quote Within Excel

Private Sub Command1_Click()

Dim rst As DAO.Recordset

Dim db As DAO.Database

Dim result As Variant

Dim XMLHTTP As Object

Dim Rdate As Date

Dim Rsymbol As String

Dim Rdescr As String

Dim Rprice As Double

Dim Rdiv As Double

Dim Ryld As Double

Dim Rvol As Long

Dim ipos1 As Long

Dim ipos2 As Long

Dim ipos3 As Long

Dim shttp As String

Set db = CurrentDb

Set rst = db.OpenRecordset("Select * from M_Security where auto_price=true")

Do While Not rst.EOF

' in the line below &f=d1nsl1dya2 this is the part that tells yahoo what type

' of data to download and the various attributes you want to retrieve.

' the options chosen are:

' d1= Last Trade Date

' l= last trade (share price)

' a2=average daily volume

' see end of example for link to complete list of attributes

shttp = "http://download.finance.yahoo.com/d/quotes.csv/q?s=" & _

rst![Security Symbol] & "&f=d1nsl1dya2&ignore=.csv"

' the next 5 lines were the difficult part to get yahoo to send the quotes

MsgBox Error$ & " MailIt "

GoTo Exit_MailIt Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")

XMLHTTP.Open "GET", shttp, False

' if no date then skip

If Mid(result, 2, 3) = "N/A" Then

' parse csv string to extract data

' get quote date first

Rdate = Mid(result, 2, 9)

' next get security name

ipos2 = InStr(ipos1 + 2, result, Chr(34) & "," & Chr(34))

Rdescr = Mid(result, ipos1 + 3, ipos2 - (ipos1 + 3))

' next is the security symbol

Rsymbol = Mid(result, ipos2 + 3, ipos3 - (ipos2 + 3))

' then the last stock price

ipos1 = InStr(ipos3 + 2, result, ",")

Rprice = Mid(result, ipos3 + 2, ipos1 - (ipos3 + 2))

' now the dividend

ipos2 = InStr(ipos1 + 1, result, ",")

If Mid(result, ipos1 + 1, ipos2 - (ipos1 + 1)) = "N/A" Then

Rdiv = Mid(result, ipos1 + 1, ipos2 - (ipos1 + 1))

' then the dividend yield

ipos3 = InStr(ipos2 + 1, result, ",")

If Mid(result, ipos2 + 1, ipos3 - (ipos2 + 1)) = "N/A" Then

' bad yield value

Ryld = Mid(result, ipos2 + 1, ipos3 - (ipos2 + 1))

' last is the volume

Rvol = Mid(result, ipos3 + 1, Len(result) - ipos3 + 1)

' now update the tables with the new quote data

rst![security name] = Rdescr

Set rst = Nothing

Here is a complete list of all the attribute options:

That was pretty simple after getting the code correct to make the yahoo finance website response to me in something other than binary data. The CSV part of the call to yahoo finance was key to the solution.

Below are a bunch of different financial functions for various stock data from my Excel Spreadsheet VBA Code.

Function buyyld(div, buyprice)

' ---figure out current yield

buyyld = div / buyprice

Function CurYld(div, share) As Variant

CurYld = div / share

Function DivAmtQ(div, shares)

' ---get dividend amount

DivAmtQ = div * shares

Function DivQ(annualdiv) As Variant

On Error GoTo endit

If annualdiv = "N/A" Then Exit Function

Function FromAvg200(strticker As String) As Variant

'-----retrieve 200 day moving average

Dim strurl As String

Dim strcsv As String

strurl = "http://download.finance.yahoo.com/d/quotes.csv/q?s=" & strticker & "&f=m5&ignore=.csv"

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "GET", strurl, False

FromAvg200 = Left(strcsv, 5)

Set http = Nothing

Function FromAvg50(strticker As String) As Variant

'--- retrieve 50 day moving average

Dim strurl As String

Dim strcsv As String

strurl = "http://download.finance.yahoo.com/d/quotes.csv/q?s=" & strticker & "&f=m8&ignore=.csv"

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "GET", strurl, False

Set http = Nothing

Function DivAmt(strticker As String) As Variant

'--- retrieve quarterly dividend amount

strurl = "http://download.finance.yahoo.com/d/quotes.csv/q?s=" & strticker & "&f=d&ignore=.csv"

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "GET", strurl, False

'MsgBox strURL & strTicker & strCSV

Set http = Nothing

Function StockQuote(strticker As String, Optional dtDate As Variant)

'--- get stock quote

' Date is optional - if omitted, use today. If value is not a date, throw error.

If IsMissing(dtDate) Then

If Not (IsDate(dtDate)) Then

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

Dim dbClose As Double

http.Open "GET", strurl, False

Set http = Nothing

We have a downloadable example of a simple function to get stock quotes using Yahoo Finance's API. Checkout our new Form API Interface Database.

MS Access 2000 Through 2016 and Office 365 & Sharepoint

Microsoft Access 2007, 2010, 2013 & 2016