Excel Addin to pull prices from Bloomberg

[rukawa]

I wanted to pull prices from Japanese stocks automagically in an Excel spread sheet. The SMF addin doesn't really work for Japan. I looked at thread on Google tips:

http://www.cornerofberkshireandfairfax.ca/forum/general-discussion/google-spreadsheet-tips-and-tricks/40/

 

Its pretty great but I ran into problems with ImportXML and I preferred to use Excel. So I created my own addin using ExcelDNA and C#. The attached addin was meant to pull prices from Bloomberg but it also has functionality to pull data from any site using xpath selectors. There are two xll addins: one for 32-bit Excel and one for 64-bit Excel. In addition the Examples.xlsx contains examples of how the functions are called and the attached word document provides some documentation. I've also include a zip of my full visual studio project for those who want to modify it.

 

getPrice(ticker) – goes to Bloomberg page https://www.bloomberg.com/quote/ticker and grabs the price from that page using the selector //div[@class=’price’]

 

getName(ticker) – same as get price except that it grabs the name using the selector //h1[@class=’name’]

 

getBloombergAttribute(ticker, selector) - goes to Bloomberg page https://www.bloomberg.com/quote/ticker and grab text from xpath selector specified in function call

 

getAttribute(url, selector) – goes to arbitrary url and grabs text from whatever selector is specified in function call argument

 

The easiest and most useful one is getPrice. And an example call is:

 

=getPrice("7922:JP")

examples.xlsx

Excel_Addin_to_pull_prices_from_BLoomberg.docx

GetBloombergData-AddIn-packed.xll

GetBloombergData-AddIn64-packed.xll

[rukawa]

Bloomberg changed their site and broke my addin. I fixed it. The attachments in this thread should work now.

[Phaceliacapital]

Thanks a lot. Does this also work for Google Docs?

[rukawa]

Thanks a lot. Does this also work for Google Docs?

 

No. But google docs has even easier solutions....see here:

http://www.cornerofberkshireandfairfax.ca/forum/general-discussion/google-spreadsheet-tips-and-tricks/40/

 

for google docs if the call used to be:

=ImportXML("https://www.bloomberg.com/quote/BOCS:SP", "//div[@class=price]")

 

But now after bloomberg change their site it should be something like:

=ImportXML("https://www.bloomberg.com/quote/BOCS:SP", "//span[starts-with(@class, 'priceText__')]")

 

Both solutions are based on looking for stuff on webpages using xpath expressions.

 

The first expression says find a div element where the class = price

 

The second says find a span element where the class starts with the text priceText__.

 

If you use Chrome and go to the bloomberg site https://www.bloomberg.com/quote/BOCS:SP

 

and right click on the price and click "Inspect" it will give you some idea of what is being done.

[leftcoast]

Hey rukawa - The GetBloombergData add-in was working great for me for the past 6 months or so and was a nice replacement for the Yahoo SMF add-in. Thanks for sharing it! Unfortunately, when I upgraded to Office 365 last week, the add-in stopped working. Every time I start Excel now, I get the attached dialog box:

 

The file format and extension of 'GetBloomberData-AddIn-packed.xll' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open in anyway?

Clicking 'Yes' opens a worksheet full of random characters. Clicking 'No' dismisses the dialog box. But in either case, the getPrice and other add-in functions no longer work. Any thoughts?

[rukawa]

Sorry but I have no clue. This is all based on Excel-Dna so I would suggest checking their website to see if Office365 is breaking anything.

[leftcoast]

Figured it out... I think the upgrade must have installed a 64-bit version of Excel. It was barfing when trying to load the 32-bit xll file. I replaced it with the 64-bit xll and it works fine now.

[rukawa]

This Addin is no longer working. Bloomberg killed robotic usage of their site. Not sure if there is a way to fool it but for now the addin is not working.

[leftcoast]

I noticed that. It was good while it lasted.

 

Fortunately, Microsoft is adding stock quotes to Excel's built-in functionality. Works for most stock markets world-wide, but does not cover other types of securities like warrants and options.

[rb]

This Addin is no longer working. Bloomberg killed robotic usage of their site. Not sure if there is a way to fool it but for now the addin is not working.

Have you tried adding a time delay between your requests? When I built a google finance scraper that seemed to do the trick of fooling it.

[rukawa]

This Addin is no longer working. Bloomberg killed robotic usage of their site. Not sure if there is a way to fool it but for now the addin is not working.

Have you tried adding a time delay between your requests? When I built a google finance scraper that seemed to do the trick of fooling it.

 

I think you mentioned IB api before...did you ever get that working in an excel spreadsheet.

[rb]

Oh yea, the IB API works really well with Excel. The problem with the IB API is very good. In the sense that it lets you do what it was intended and nothing else. A lot of it is restricted by data policies they have. So quotes are good. Creating trading macros with integration is good. But scraping data or creating screeners doesn't work at all.

 

Basically the IB APIs will help you customize your TWS experience some by using Excel, I honestly haven't invested a lot in it because for that sort of stuff in TWS is good enough for me. If you do a lot of multi legged trades or quant strategies the IB APIs will be awesome. But i don't really do that stuff.

 

I just want a good screener that doesn't cost a fortune that I can match to a whatch list and create alerts for. I got pretty close to doing that using Google Finance but then they went ahead and ruined that whole thing.