Jump to content

Excel Addin to pull prices from Bloomberg


rukawa
 Share

Recommended Posts

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

Link to comment
Share on other sites

  • 3 months later...

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.

Link to comment
Share on other sites

  • 5 months later...

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?

GetBloombergError.thumb.JPG.e8f6b38c1da892780709831e3f4e0d37.JPG

Link to comment
Share on other sites

  • 2 months later...

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...