Skip to main content

Bloomberg JSON data into Libreoffice Calc

LibreOffice Calc has no inbuilt stock market functions, and a popular plugin which offered those has stopped working along with changes to Yahoo Finance.
Luckily, we can get the latest quotes from Bloomberg.
[2018-12-15] Bloomberg Finance is, understandably, blocking multiple simultaneous requests. A more flexible solution is using a Python Stock Scraper.  


Step 1. Enable regular expressions in Libreoffice Calc.

Go to:
Tools - Options - LibreOffice Calc - Calculate
Select:
Enable regular expressions in formulae

Step 2. Use 'WEBSERVICE' function to download data into a cell.

Example: to download last month's data for Mizuho Group on the JPX:
=WEBSERVICE"https://www.bloomberg.com/markets/chart/data/1M/8411:JP")
We'll call the cell containing the downloaded JSON string <dataCell>

Step 3. Use 'SEARCH' function to isolate desired data.

The last quote usually takes the format, "99.99]]"
The start position of the last quote can be returned into a cell with the function:
=SEARCH("[0-9]+.[0-9]+\]\]",<dataCell>)
The end position can be returned into another cell with the function:
=SEARCH("]]",<dataCell>)
The MID function can then be used to populate another cell with the substring containing the quote.
=MID(<dataCell>, <startPositionCell>, <endPositionCell>-<startPositionCell>

Notes

  • (Unfortunately) Calc has no inbuilt JSON functions.
  • Importing Bloomberg data into Calc is more reliable than into Google sheets. Bloomberg servers sometimes reject requests from Google as contravening terms of service.

Comments

Popular posts from this blog

Transcode to PSP using Handbrake

Source: Handbrake 0.9.9.5530 64-bit edition Target: (Phat) Playstation Portable PSP-1000 , System Software: 6.60 Many internet articles on how to transcode video to PSP using Handbrake have not worked for me. Even the most helpful are incomplete. I hope this post will help fill in the blanks. There is no longer any PSP preset for Handbrake, but from what I can gather, the preset had only limited success as the x264 encoder would change syntax and settings between versions. Other presets that may have worked before, like 'iPod' and 'Apple-Universal' now do not. Here is what worked for me, step by step:

Firefox History Statistics - Extracting from Places.sqlite

If you want to take a look at Firefox surfing activity, the about:me add-on is a good start. However, it presents only one view of data and is thus limited in its ability to present more detailed statistics. We will view that data in a different program. So let's first extract it from the browsing history stored in the Places.sqlite file into a CSV file using a Firefox add-on. Step 1 - Locate and copy Places.sqlite to a working location On Windows machines, Places.sqlite is found in a directory similar to: C:\Users\User1\AppData\Roaming\Mozilla\Firefox\Profiles\ .default\places.sqlite Copy the file to another location. The database will be locked while using Firefox, and the SQLite plugin we will use to open it.