Pulling Stock Prices into Excel by Formula (VBA code provided)

I am an investment tinkerer. This means I sometimes follow the bad practice of looking at my portfolio value. This is generally not a smart thing to do.

Why is this bad?

I’m a huge fan of Burton Malkiel’s advice to invest in low-cost ETF funds and not stress more than that. And more so, to not look at how your investments are doing very often at all! This is, generally, a vastly better approach as it prevents you from reacting to market swings in your investment plans (reactions that, as Malkiel shows in his book, are generally a terrible idea). For most family and friends I recommend low-cost target retirement funds where you can buy and not have to think about it again at all.

It’s fun though, so while I restrain myself from reacting excessively, I enjoy checking my portfolio, or simply doing ad-hoc analysis myself. I do this in Excel to enable fast analysis. This is mostly pretty painless, but with different accounts from different sources (e.g. 401(k), IRA, ROTH IRA) it can mean pulling a lot of values manually.

Several years ago I cobbled together a few VBA scripts to speed this up by pulling stock quotes automatically. The sources of these quotes have unfortunately, one by one, shut down or gotten more complex/cumbersome. For a while I updated my scripts with clunky hacks, but eventually even those broke.

A new VBA solution

So today I decided to update it with an approach that’s hopefully a bit more robust. A very useful blog article at https://programmingforfinance.com/2018/02/getting-updated-stock-quotes-into-excel/ noted that the Bloomberg API can be queried for JSON input and had code to pull this. I took this as a starting point and generalized it a bit, making it available as a cell function, which I’m sharing here.

Usage of the Function

Cell Popup - Pulling Stock Prices into Excel by Formula (VBA code provided)

Using this is simple and straightforward. The formula is available in-cell and is used by passing the stock ticker symbol into the formula (if you enter the symbol manually it must be a string; i.e. in quotes).

A populated version of the formula is shown here:

Cell popup populated - Pulling Stock Prices into Excel by Formula (VBA code provided)

And when executed gives a value back as one might expect. The results can thus be copied and/or dragged as typical with any formula:

call result - Pulling Stock Prices into Excel by Formula (VBA code provided)

Code to set the function up

To set up the formula there are three steps needed.

  1. Download and import the json parser from https://github.com/VBA-tools/VBA-JSON
    • This means download the .bas file, open up the Developer window in Excel (Alt + F11), and import the .bas file as a new module.
  2. Enable the Microsoft Scripting Runtime and the Microsoft WinHTTP Services references as seen below (Tools->References from the Developer window)
VBAReferencesToAdd - Pulling Stock Prices into Excel by Formula (VBA code provided)
The bottom two checked items should be added.

3. Create a new Module and paste this code into it:

Public Function getBloombergPrice(symbol As String) As Variant
    Application.StatusBar = "Pulling Bloomberg Stock data for " & symbol
    'Send request to Bloomberg with stock symbol
    Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    ' If there's a space in the symbol, replace it with a slash (example: BRK B)
    If InStr(symbol, " ") Then
        symbol = Replace(symbol, " ", "/", compare:=vbTextCompare)
    End If
    myrequest.Open "Get", "https://www.bloomberg.com/markets/api/bulk-time-series/price/" & symbol & "%3AUS?timeFrame=1_DAY"
    'Parse JSON
    Dim Json As Object
    Set Json = JsonConverter.ParseJson(myrequest.ResponseText)
    'Pull lastprice value out
    getBloombergPrice = Json(1)("lastPrice")
    Application.StatusBar = False
End Function

That’s it! Once this is done you can pull current prices into Excel with ease.

Leave a Reply