Getting Updated Stock Quotes into Excel (Without Google or Yahoo)

Click to Download Workbook: Stock Quotes

This is a method to get updated stock quotes from Bloomberg into Excel by parsing JSON. Let’s examine the steps to doing this.

For those who don’t know, JSON stands for JavaScript Object Notation. JSON is also a data format. We will be working with JSON arrays, meaning everything will be separated into key and value pairs. This is also known as an associative array. We will need an external library to parse the JSON from Bloomberg’s website. Below is an example of a JSON array if anybody’s curious.

Step One: Import JsonConverter into Excel.

Download the project found here (Thanks to timhall):

https://github.com/VBA-tools/VBA-JSON

Within the VBA editor, right click on modules, and then insert file. Then select the JsonConverter.bas file on your desktop. This is what you should see:

You will also need to add two references: Microsoft WinHTTP Service 5.1 and Microsoft Scripting Runtime.

Step Two: Get JSON and Parse:

I wrote the following subroutine to loop through a list of tickers and extract the current stock price on Bloomberg (consistently updated).

Output:

*Note you can add more tickers to the list as this is a dynamic spreadsheet.

About the author

programmingforfinance

Hi, I'm Frank. I have a passion for coding and extend it primarily within the realm of Finance.

View all posts

19 Comments

  • Thank you for this helpful video! Is there a way to make the last price a real time quote instead of a delayed one?

  • Thanks Frank. Terrific work!
    I made a slight modification to enable Canadian stocks. A Canadian stock should have a suffix of :CN. In your getData function I added an if statement as shown below –
    Dave
    symbol = x

    Set myrequest = CreateObject(“WinHttp.WinHttpRequest.5.1”)
    If Right(symbol, 3) = “:CN” Then
    myrequest.Open “Get”, “https://www.bloomberg.com/markets/api/bulk-time-series/price/” & Left(symbol, Len(symbol) – 3) & “%3ACN?timeFrame=1_DAY”
    Else
    myrequest.Open “Get”, “https://www.bloomberg.com/markets/api/bulk-time-series/price/” & symbol & “%3AUS?timeFrame=1_DAY”
    End If
    myrequest.Send

    • Hi,
      Is it possible to have the Indonesian stock price also? I can’t code, so you may help this problem? Thanks.
      Aldo–

    • Hi,
      Thanks OP, very nice work!

      I got a more general approach to the country specific query:

      Set myrequest = CreateObject(“WinHttp.WinHttpRequest.5.1”)
      myrequest.Open “Get”, “https://www.bloomberg.com/markets/api/bulk-time-series/price/” & symbol & “?timeFrame=1_DAY”
      myrequest.Send

      In this way, any country suffix will directed the query to the right country list.
      Caveat – : a :US suffix will be necessary in your American stock list, i.e. MSFT:US. Canadian would be LEAF:CN.
      BTW is there a list of suffixii (?) that Bloomberg uses? I tried :UK and :NL but to no avail. Thanks, Frans
      BTW2: take care when copying from this box directly into VBA. Quotes will be misformed, and the code won’t run until edited.

    • Thanks for the Canadian quotes!
      However, when I tried it I got an error at the following line:

      i = Json(1)(“lastPrice”)

      Error message: Run-time error “13”:
      Type mismatch

      and I copied exactly what you wrote, like this:
      Set myrequest = CreateObject(“WinHttp.WinHttpRequest.5.1”)
      If Right(symbol, 3) = “:CN” Then
      myrequest.Open “Get”, “https://www.bloomberg.com/markets/api/bulk-time-series/price/” & Left(symbol, Len(symbol) – 3) & “%3ACN?timeFrame=1_DAY””
      Else
      myrequest.Open “Get”, “https://www.bloomberg.com/markets/api/bulk-time-series/price/” & symbol & “%3AUS?timeFrame=1_DAY”””
      End If

      myrequest.Send

      Dim Json As Object
      Set Json = JsonConverter.ParseJson(myrequest.ResponseText)

      i = Json(1)(“lastPrice”)
      ws.Range(Cells(n, 2), Cells(n, 2)) = i

      n = n + 1
      Next x

      I find it strange since I double-checked the orginal version with the same line, which works!

      Any idea?

  • Hi Frank,
    Great tutorial. Is there anyway to get access to the changing prices at 5 minute intervals rather than the “lastprice” so that they could be used to update a graph for each ticker? Also is it possible to pass an argument to the query that states the time period that data is required for?
    Thanks,
    Chris

  • Hi Frank
    I think your tutorial is great but I have a problem running Json. Every time I get a Run Time Error 10001: Error Parsing Json:<!DOCTYPE H. Any ideas what the problem might be?
    Regards
    Terry

  • Hello Frank,
    Thank you for this tutorial.

    Is it possible de get also the index like the DownJones or Nasdaq and currency change, using the same methodolohy? I am not an expert of coding…
    I am trying using “https://www.bloomberg.com/markets/api/bulk-time-series/price/EURUSD%3ACUR?timeFrame=1_DAY”
    instead of “%3AUS” but It doesn’t work, and It is highlighting me :
    i = Json(1)(“lastPrice”) .

    Regards,
    Oliver

  • Thank you Frank for the wonderful tutorial. I was able to adapt it for my use. All the ticker symbols I enter seem to be fine except for one that I found a discrepancy between JSON and Bloomberg.com stock quote. The symbol is FRGXX, JSON returns a quote of $1.83 and Bloomberg $1.00. Any idea what the issue is?

    Thanks,
    Ken

  • Hello Frank… thank you for such a great video & your script… It’s very helpful.
    I am getting the following error-

    Run Time Error ‘10001’:
    Error Parsing Json:
    <head
    ^
    Expecting '{' or '['

    It goes away sometimes if I keep trying… But then it again gets stuck after running down few more tickers….
    Can you please suggest how can I resolve this ?
    Thank you again for your great work.

  • I am getting runtime error 2147012739
    An error occured in secured channel support.

    Please help me to resolve this?

    Thank You

  • Hi Frank,
    First, thank you for your code. I used for few months ago.

    but,
    maybe you have to uprade your code, because i have error now. maybe the bloomberg website has modify his content.

  • I’m getting the same error. When I view the response text, the title of the html page is “Terms of Service Violation.” I am guessing that Bloomberg updated their API as did Yahoo and Google. Am I correct? Where do we go from here?

  • Hi Frank!
    Till recently, I have been using the code for more than one year to get Bloomberg historical data.
    However, it does not work anymore (Bloomberg reports “Terms of Service Violation…”)
    Any solution / comment ?
    Thanks and regards

  • Hi Frank!

    Just to add (to my previous post):

    1. I just saw that my problem is the same as reported by Terry and Saagar. However I reported what I see in the debugger (ResponseText)

    2. It is worthwhile to mention that if I enter the URL manualy in the browser, it works. It does not work when called from Excel vba.

    3. Maybe, it has something with changes on my computer – I (just think) I am experiencing this after I changed to IE 11.1 and Office 365.

    regards
    Marko

Leave a Reply

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