Friday, 10 November 2017

Bloomberg JSON data into Google Sheets

Import Bloomberg information into Google Sheets.

Problem(s)

  1. The GOOGLEFINANCE function in Google Sheets does not cover all stock markets. Notably missing are quotes from Japan Exchange (JPX) and Singapore Exchange (SGX).
  2. Google Sheets does not have a native function to import JSON-formatted data.

Solution



  1. In Google Sheets, Click Tools-Script Editor.
  2. Copy, paste, and save the following script:
/**
*Returns the last word in a string, given a split/separator value. e.g. =EndWord("breathe, rainbow, four-fifty", ",") returns "four-fifty".
*
*@param {String} input string
*@param {String} split/separator value
*@return {String} the string/word after the final split/separator value.
*@customfunction
**/
function EndWord(inputString, splitString) {
  return inputString.split(splitString).pop();
}
  1. In similar fashion Install the ImportJSON() function. EndWord() and ImportJSON() are our new Google Sheets custom functions.
  2. [Examples] In Google Sheets, typing this into a cell:
=ImportJSON("https://www.bloomberg.com/markets/chart/data/1D/DBSSTI:SP","/prev_close", "noHeaders")
...will return the previous closing price of the Nikko AM STI ETF on the SGX (in local currency), while this:
=EndWord(ImportJSON("https://www.bloomberg.com/markets/chart/data/1D/DBSSTI:SP","/data_values", "noHeaders,noTruncate"), ",")
will return the last price.
  1. Use Bloomberg.com and the URL in the ImportJSON function to find and test the other symbols in your portfolio.
  2. Edit the examples above for other cells.

Nota Bene

While it's more efficient to get the previous close, not every quote returns open/close prices. Notably affected are many mutual funds and tōshin shintaku (投信信託). Getting the latest price is less efficient but more reliable.

References

No comments:

Post a Comment