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

Dismissing Racism

Whenever white people kill people of colour, as in 2021's anti-Asian shootings in Atlanta Georgia , this sort of counter-commentary appears: "Since the killing of six Asian women who worked in massage parlors in Atlanta, the media has amplified the false narrative that “white supremacy” is to blame.  ... official crime stats show that white people are significantly underrepresented in terms of the violent crime threat they pose to Asians."  ... citing FBI statistics , whereas whites comprise 62% of the population, they committed 24% of crimes against Asians in 2018.  In comparison, blacks, who comprise 13% of the population, committed 27.5% of all violent crimes against Asian Americans in 2018.  So clearly, white people do not represent the biggest crime threat to Asian Americans." Not only is this an attack on the media and its imagined agenda, it also implies that Asians can't tell who's assaulting them.
Post a Comment
Read more