Bloomberg Data

Discussions about the testing and simulation of mechanical trading systems using historical data and other methods. Trading Blox Customers should post Trading Blox specific questions in the Customer Support forum.
Post Reply
Keith H
Contributor
Contributor
Posts: 4
Joined: Fri Sep 24, 2010 1:47 pm
Location: IN

Bloomberg Data

Post by Keith H » Mon Sep 27, 2010 12:30 pm

Hello, I'm test driving TB this week and wanted to hear from anyone who uses Bloomberg for data. I am searching for the best way to download the info. Someone from the Bloomberg heldesk created a spreadsheet for me to turn single stock data into text format. However, I think there has to be a better way. Any advice is greatly appreciated.

Best,
Keith

sluggo
Roundtable Knight
Roundtable Knight
Posts: 2986
Joined: Fri Jun 11, 2004 2:50 pm

Post by sluggo » Mon Sep 27, 2010 12:40 pm


Keith H
Contributor
Contributor
Posts: 4
Joined: Fri Sep 24, 2010 1:47 pm
Location: IN

Post by Keith H » Mon Sep 27, 2010 12:55 pm

Thanks for the quick reply. I used the search initially and came up with the same results. A few of the links don't work for me, perhaps they are in the customer area? In the links that I could read, a couple posters had the same question; but, it went unanswered. In my email exchange with Tim, prior to downloading the TB trial, he said that there were customers that used Bloomberg and to speak with them on how they handle the downloads. So, here I am....

Thanks

szarzycki
Full Member
Full Member
Posts: 22
Joined: Fri May 25, 2007 7:39 am
Location: nyc

Post by szarzycki » Mon Sep 27, 2010 9:55 pm

you need a macro to export those data series to separate CSV files, which can be picked up by TB. thats what i would do.

gunter
Roundtable Fellow
Roundtable Fellow
Posts: 83
Joined: Wed Aug 24, 2005 7:25 am
Location: George Town, Cayman Islands

Post by gunter » Tue Sep 28, 2010 9:17 am

Have a look at the attached spreadsheet for the formulas for any individual securities.

However, you need access to a PC with Bloomberg installed, otherwise the formulas do not work.

I did not find a way to do this for more than one security at a time, but that does not mean that there isn't one. Granted, I also did not spend much time looking for it.

If all else fails (or before then), you can also contact the Bloomberg Help Desk via your Bloomberg terminal.

Good luck,

gunter
Attachments
Bloomberg historical pricing formula.xls
(119.5 KiB) Downloaded 283 times

gunter
Roundtable Fellow
Roundtable Fellow
Posts: 83
Joined: Wed Aug 24, 2005 7:25 am
Location: George Town, Cayman Islands

Post by gunter » Tue Sep 28, 2010 10:08 am

Rereading the post, I see you already have the spreadsheet from Bloomberg.

After thinking about this some more, you can modify this spreadsheet to include multiple securities, although you will most probably be using up all of your columns or lots of tabs. You will just need to have a central database for all the securities you want listed and link your formulas to your database.

Once that's done, you will need to set up your macro.

Cheers,

gunter

Keith H
Contributor
Contributor
Posts: 4
Joined: Fri Sep 24, 2010 1:47 pm
Location: IN

Post by Keith H » Tue Sep 28, 2010 1:20 pm

Thanks, gunter. I have been playing with the Bloomberg add on to Excel. I think I may have a handle on it, now. One limitation I have found is on intraday data, the macro will only download approx 6 months.

Would I be able to save the excel file as a .csv file and have it feed properly into TB? Almost sounds too easy given what I worked through this morning.

Thanks Again,
Keith

gunter
Roundtable Fellow
Roundtable Fellow
Posts: 83
Joined: Wed Aug 24, 2005 7:25 am
Location: George Town, Cayman Islands

Post by gunter » Tue Sep 28, 2010 2:32 pm

If you plan on saving to .csv, then your format needs to be the same as the TB format. If not, the data will not import correctly.

You will need to align your cells so that they are in the same sequence as what TB uses. Then saving to csv should work. Unfortunately I have not yet done anything like that, so can't help there.

Cheers,

gunter

Keith H
Contributor
Contributor
Posts: 4
Joined: Fri Sep 24, 2010 1:47 pm
Location: IN

Post by Keith H » Tue Sep 28, 2010 4:58 pm

Right now, when I look at the excel file saved as .csv in Notepad, the data looks like this: "MM/DD/YYYY HH:MM,O,H,L,C,V"

10/19/2009 09:30,39.58,40.4,39.42,40.11,167415
10/19/2009 09:45,40.1,40.22,39.69,39.75,107626
10/19/2009 10:00,39.75,40.23,39.62,40.17,86452
10/19/2009 10:15,40.21,40.56,40.04,40.51,185308
10/19/2009 10:30,40.52,40.59,40.11,40.114,106595

If I'm reading the format right that "space" between the year and hour is going to be an issue with TB. The only way I have been able to "fix" this is by the "=TEXT" function and then combining all of the cells in a given row, into one cell. From there I can copy and paste into a .txt file

Not as smooth as I would like.

tovetski
Senior Member
Senior Member
Posts: 46
Joined: Fri Jan 13, 2006 10:49 am
Location: Montréal, Québec, Canada

Bloomberg Data

Post by tovetski » Sat Oct 09, 2010 10:04 pm

Hi all,

The best way to extract any significant amount of data from bloomberg has to be done through the API in C, C#, .NET or Java. I've done it in C and it works very well. You'll probably get around to learning one of these ways once you get tired of dabbling in EXCEL VBA to extract bloomberg data.
If you plan on going live, you'll have to build the logic to download, store, and split and distribution adjust your data the way you like as bloomberg might not be doing it the best way.

CSI is a good choice for a backtesting universe if you get the delisted company add-on. Then again, it probably doesn't matter as much if you have short-term trading in mind. You can easily decide how to adjust your price series for splits and distributions and it is very flexible if you're willing to learn a bit of perl. I feel its easier to build a testing environment closer to reality by using CSI rather than Bloomberg.

Hope this helps,

Pierre

Post Reply