Get Stock Quote In Excel (Method 1)

Get Stock Quote In Excel (Method 1)

This entry is going to be rather long. If you want the quick answer, just jump to Step 4 below and copy/paste the enabling code.

I had a need to create an excel file where there is a live update of stock price from the market.The requirement is to have a simple API, free of charge, that does not require authentication or registration. Something quick and simple.

Step 1: Get the right API

Googling i found that there are tons of different APIs but as far as i can see they all require some sort of valid credentials to be used. Extra complication. Bummer…

Looking closer I found an unofficial Google API. According to Google it is deprecated since 2011 but somehow it still works.
The HTTP Get request is simple enough. To fetch the Google stock price you can user the following:

http://www.google.com/finance/info?q=NASDAQ:GOOG

Or the following (without the NASDAQ):

http://www.google.com/finance/info?q=GOOG

The return value is a malformed JSON document that has extra “//” at the beginning. Cleaning it up it looks something like this:

[ { "id": "304466804484872" ,
    "t" : "GOOG" ,
    "e" : "NASDAQ" ,
    "l" : "719.41" ,
    "l_fix" : "719.41" ,
    "l_cur" : "719.41" ,
    ...
 } ]

Step 2: Get the right Excel VBA helper code

Since I am using Excel on Mac, the very useful function of WebService is not available. A while back I wrote an entry about myWebService function. It is a simple User Defined Function that can be used to call simple REST APIs just like the Google Finance one.

The code to call the Google finance API is:

Dim json As String
json = myWebService("http://www.google.com/finance/info", "q=" + stock)

Step 3: Understand how to fake-parse JSON responses

There are some VBA libraries that provide full JSON parsing (here is one such library). I have not used these before and did not want to make this Excel file too complicated. So instead I searched for an easy way to tokenize the JSON document and find the specific entry that represent the current price.

For that I used the command Split with delimiter of double quote.

The code to tokenize the json response is:

Dim tokens() As String
tokens = Split(json, """")

 

And finally, i needed to iterate across all the tokens and search to my specific token that represents the current price. It is called “l_cur”. The code for that is as follows:

Dim i As Integer
For i = 0 To UBound(tokens) - 1
  If tokens(i) = "l_cur" Then
    getStockPrice = tokens(i + 2) ' ToDo Should check that there are enough tokens, and that this token is numeric value '
    Exit For
  End If
Next

 

Step 4: Bringing it all together

With that in mind I have everything we need. The final code introduce a new User Defined Function called getStockPrice that accepts one parameter that represents a stock symbol and returns the current price of the stock.

The code also includes all the prerequisites including the code for myWebService.

Copy the following code into a VBA editor (see instructions in previous entry).

Option Explicit
' execShell() function courtesy of Robert Knight via StackOverflow '
' http://stackoverflow.com/questions/6136798/vba-shell-function-in-office-2011-for-mac '
Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long

Function execShell(command As String, Optional ByRef exitCode As Long) As String
  Dim file As Long
  file = popen(command, "r")
  If file = 0 Then
    Exit Function
  End If

  While feof(file) = 0
    Dim chunk As String
    Dim read As Long
    chunk = Space(50)
    read = fread(chunk, 1, Len(chunk) - 1, file)
    If read > 0 Then
      chunk = Left$(chunk, read)
      execShell = execShell + chunk
    End If
  Wend
  exitCode = pclose(file)
End Function

Function myWebService(sUrl As String, sQuery As String) As String
  Dim sCmd As String
  Dim sResult As String
  Dim lExitCode As Long
  sCmd = "curl --get -d """ + sQuery + """" + " " + sUrl
  sResult = execShell(sCmd, lExitCode)
  ' ToDo check lExitCode '
  myWebService = sResult
End Function

Function getStockPrice(stock As String)
  Dim json As String
  json = myWebService("http://www.google.com/finance/info", "q=" + stock) ' get the json response '

  Dim tokens() As String
  tokens = Split(json, """")

  getStockPrice = "Unknown"
  Dim i As Integer
  For i = 0 To UBound(tokens) - 1
    If tokens(i) = "l_cur" Then
      getStockPrice = tokens(i + 2) ' ToDo Should check that there are enough tokens, and that this token is numeric value '
      Exit For
    End If
  Next
End Function

 

Step 5: Check that all is working well

Return the Excel Worksheet and type in one of the cells the following:
=getStockPrice("GOOG")

You should see the current price of the Google stock displayed.

getStockPrice-google


1 thought on “Get Stock Quote In Excel (Method 1)”

Leave a Reply

Your email address will not be published. Required fields are marked *