Week 6 Trading
This week I spent a lot of time downloading and manipulating Bloomberg data in Excel. In spirit of our upcoming project proposals, I thought I’d share how to use the Bloomberg Excel Add-In. The terminal is a very good source of data. I will also share a couple of graphs I created to think about when trading wheat.
First off, go to the Bloomberg (BB) terminal – it’s located in CLC 222, the Sauder computer lab in the Canaccord Learning Commons.
Open excel and click on the Bloomberg Add-In (on the excel ribbon). There are various ways to input formulas here, including using the Data Wizard – probably the simplest method.
Whether you are using the data wizard or typing the formulas directly into your spreadsheet, it is important to understand how the formulas work, so that you can adjust the formulas to your needs.
There are only 3 excel formulas for BB and a ton of data fields. Using wheat as an example, the formulas are:
1) Bloomberg Data Point (BDP) returns a single data point to your spreadsheet. For example, = BDP(“W Z3 Comdty”,”Px_Last”) returns the last price for the December wheat contract. “W Z3 Comdty” indicates the security of interest. “Px_Last” is a data field and it indicates the kind of information needed. I will shortly explain how to search for data fields.
2) Bloomberg Data History (BDH) returns the historical data of a security. For example, = BDH(“W Z3 Comdty”,”Px_Last”,”10/25/2012”, ”10/25/2013”) gives the December 2013 wheat contract’s last price for each day from 10/25 2012 to 10/25/2013.
3) Bloomberg Data Set (BDS) returns multi cell descriptive data to your spreadsheet and follows a similar format as the BDH formula.
The quotation marks can be omitted if a cell is selected instead. So for example, if cell A1 contains: W Z3 Comdty, then you can use the formula =BDP(A1,”Px_Last”) instead of = BDP(“W Z3 Comdty”,”Px_Last”).
To search for data fields, one way is to open the BB software on the terminal and type “FLDS” and then press “GO”. Now you can search for data fields by typing information of interest, pressing “GO” and then browsing the related data fields’ codes and descriptions.
With this information, you are ready to look up and download almost anything from the BB terminal and your data will be live. It’s so exciting!
Here’s an example of what you can do. I was interested in the historical physical prices of wheat. As we learned in class, these are equal to the future prices at contract expiration. So I used the Bloomberg Add-In to gather this information.
First I listed past contracts and the dates at which they expired (I used normal excel formulas for this part). Then I used a BB formula to get the price of wheat for the contract indicated at the dates indicated. Then I repeated the formula for each row. This is how my spreadsheet looks like.
Here’s how the results look like once graphed:
The graph represents derived historical physical prices of wheat.
My Bloomberg tutorial is over. If anybody has questions or needs help downloading information for their project, please let me know.
Here’s a one more graphs on wheat prices that I created using Excel and the Bloomberg Add-In.
One Comment