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.
Labels:
Location: Koganei, Tokyo, Japan

Comments

Post a Comment

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:
4 comments
Read more

Scatterbox - build an Android Tor Socks Proxy Server

Cloak your location and create a firewall bypass device with a smartphone. 🕵Uses the Tor network . Does not require root. 1 - from Google Play, download and install: Orbot Orweb browser Socks Server Ultimate (Optional)
Post a Comment
Read more

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.
2 comments
Read more