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
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:
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:
Code to set the function up
To set up the formula there are three steps needed.
- 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.
- Enable the Microsoft Scripting Runtime and the Microsoft WinHTTP Services references as seen below (Tools->References from the Developer window)
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" myrequest.Send '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.