Hello and welcome, everyone, to the API session of the Bloomberg Essentials Online Training Program. This course will cover how to use our data research, how to search for fields, and lastly, where to find your resources regarding these different options.
So let’s begin with looking at the Bloomberg ribbon within a cell. The Bloomberg ribbon is broken down by the most frequently used tools on the left, to the more advanced options towards the right of the toolbar. Please note that all the options starting with error substitution all the way until PowerPoint are available for a Bloomberg Anywhere user only. University accounts, for example, are considered Bloomberg professional users, and hence may not have access to these tools.
So let’s start with the data navigation pane, which is the first icon within the Bloomberg ribbon. When you click on the data navigation pane, it is essentially a little wizard that allows you to easily and quickly download data for a given security within Excel. For example, I’m going to click into this little white toolbar and type in AEPL. You will notice that conveniently the auto complete feature works for me here, whereby I can select the security that I’m looking to obtain data for.
In this case, I’m going to select Apple equity. Once I select the security, a list of categories populate for me that are relevant to that particular security. So in this case, for instance, if I’m looking to download the income statement data for Apple equity, I would click into the second option within the list of categories called financial/valuation.
Once you click into that, it will now bring you further down to a list of sub-categories within the financial valuation template. In here, I can select either statement summaries, an income statement, a balance sheet, and et cetera. I would click into the income statement, which would load all line items from the income statement for a particular equity security.
There are a couple of options that I have before I output this data into Excel. I can control the settings by clicking into this little settings icon, whereby it would allow me to select the number of periods I would like the data for the income statement for, as well as other things such as other standardization options, such as currency, sorting, and the type of data. In this example, we will leave this data to output the last five fiscal years for Apple. And we’ll click back into the settings icon to close the – close the wizard.
The final step is for me to be able to take this data and drag and drop this into Excel. I will click onto the second icon, hold down the left click, and drag and drop the data into Excel, whereby all the income statement data for Apple is now outputted into my Excel sheet. Once you have the data outputted into Excel, you will notice that every data in here is actually generated as a result of a formula when you click into my cell B8.
The advantage of being able to do this is I can simply come in here and the change the security from Apple to, say for example, Google. If I simply type in the ticker for Google, which is GOOG, and hit enter, all my data will now update to show me the income statement data for Google. This summarizes the data navigation pane and the ability to easily output the data that you require for a particular security into Excel.
We are now going to go into the real-time/historical data reserves, which is the – another most popularly used method to download data into Excel. I’m going to click into the real-time/historical icon, which is the third icon to the left on the Bloomberg ribbon. This opens up the Bloomberg data wizard option. There are four different methods that you can use to be able to download data into Excel using this wizard. Market reference analytical data sets, historical end of day, historical intraday bars, as well as historical intraday ticks. We will start off with the market reference analytical data sets option.
Once you click into any of the four options within the data wizard, the first question that the data wizard would expect you as a user to answer is what security is it that you are trying to obtain data for? There are two different ways in which you can enter a security. First, you can either type in the individual ticker by clicking into the security identifier box and simply typing in the identifier or the ticker of the security. The auto complete feature works conveniently for you as a user if you are unsure of what the ticker is. I would double click and select the ticker.
Another way that you can enter in a list of securities into the wizard is by selecting a particular source that you’re looking to reference as a list of securities. For example, if I am looking obtain all the members of the Dow Jones Industrial Average Index, I would select my source to be indices, and from the drop down right under it, I would select the Dow Jones Industrial Average Index.
This would load all the 30 members of the Dow Jones Index under the box that is labeled available securities. At this point, I can either select an individual security and select add, or I can add the entire list of securities by selecting add all. Once I select add all, I will have all the 30 securities from the Dow Jones added to my selected securities section. I’m going to hit next.
I am now brought to a screen where I can dictate the kind of output data that I am looking to obtain on those 30 securities. The search text box is going to allow me to select, search, and find the relevant field pneumonic that is going to allow me to download the data. For example, if I search for last price and hit the enter key or the go key on my keyboard, this is going to bring all the relevant fields that relate to my keyword search last price.
These are certain fields in here that have a red and green arrow next to them. That is an indicator that this field is going to update real time in Excel for me when the markets are open. I’m going to select this field called last price or PX last (ph) by simply double clicking on the field, and I’m ready to hit next.
I will click next, and I am at the final step of my wizard where it is going to show me a preview of what my output will look like. When I hit finish, all my data is populated in Excel, whereby I have the ticker for all the securities that I inputted, as well as the last price for each of those securities. If you double click on any of the cells where a value is populated, you will notice that this generates a Bloomberg formula in the sense of a BDP formula, which stands for Bloomberg data point. Later in this training, we will go over how to write formulas to be able to pull data into Excel.
Great. Next we’re going to go back into the real-time/historical wizard, and this time we’re going to start by going over the historical end-of-day wizard. The historical end-of-day wizard is used to pull in any end-of-day historical value into Excel. For example, if I was looking to get the historical price of a particular equity ticker, the historical volume of a particular ticker, or let’s say a 30-day historical volatility for the oil futures, I could so using the historical end-of-day wizard.
In addition, the historical end-of-day wizard allows you as a user to be able to standardize your data set for securities trade on multiple exchanges across the globe. We’ll start by clicking into the historical end-of-day wizard, and this will bring us to the same screen as the market reference analytical data set wizard. I will come in here and select a list of securities. Hence, I will type in CLA and select the WTI crude oil current active future contract. Type in BMWGRequity for BMW as it trades on the German exchange. I would also select (inaudible) trading on the London Exchange. And finally, potash trading on the Canadian exchange.
I have selected four securities for which I am looking to pull in historical data into Excel. I will click next, and this again brings me to a field search option where I would dictate the kind of data I’m looking to output. For example, I would type in volatility 30D for the 30-day volatility historically. I would select the relevant field that I’m trying to pull into a cell historically by double clicking on that field.
And let’s say for instance I’m also looking to obtain the historical last price for these securities in Excel. I would go back into the search text box and type in last price. Select a field by double clicking on it, and I have two fields I have selected which will output towards the end of my – completion of this wizard. I’m going to select next.
In this wizard, I have now the option of inputting a start and end date to be able to generate a range of data historically. I can select a periodicity on the top right hand corner where I can toggle between daily, weekly, monthly, or quarterly data, and so on and so forth. I also have an option to select a fixed time series where I’m – whereby I’m starting on a date, in this case January 7, 2012, and I would check on the current option for my data set to always remain current.
On the other hand, I can also select relative time series where I would go back a certain number of periods from my end date, which in this case is today. For the sake of this exercise, I’m going to stick to the fixed time series and select my date range from January 1, 2012, until today. I’m ready to click next.
This option in the historical end-of-day wizard is going to allow me to standardize my data for all the securities trading on the multiple exchanges as I have selected in the first option. On the top, we start off with the currency option, where by default it would output the data into the local currency of that security. I do have an option to standardize that here by, for example, selecting the Australian dollar. I will now look at the historical price, as well as the 30-day volatility, in the Australian dollar.
The next option is to include non-trading week days or all calendar days. This option allows me to obtain a standardized list of data for securities that follow different holiday convention based on the exchanges that they’re trading on. In this case, I will select include all non-trading week days. Once I select that, my filter filler option, or filler value option, becomes active, whereby I can select to carry over the last value, output an NANA, a blank cell, or a custom output for those week days when the exchange was closed.
Great. We’re going to hit next, and the next step is going to allow me to follow DPDF settings. This applies to equity securities where it allows you as a user to customize your adjustments on any kind of capital changes that an equity stock undergoes. We will leave this to yes to follow the same settings as I have on the terminal. I will click next, and I’m at my final step of the wizard where it is showing me a preview of what my output data will look like.
I finally click finish, and it generates the entire data set for me in Excel. I’m going to select the entire data set and simply adjust the column width whereby it would fit for the amount of dates I have selected in the spreadsheet. That complete our session on the historical end-of-day component of the wizard.
We’ll go back into the real-time/historical wizard and select the third option on here called the historical intraday bars. The historical intraday bars wizard allows you to obtain data intraday for securities at a one-minute granularity level, as you see on the terminal on the IGPC tool. Once I click into the wizard, I follow the same methodology as I have been doing for the other two wizard.
I will enter in an example security. For instance, I would type in IBM and select IBM US equity. I’m going to hit next, and in here it allows me to select the kind of data that I’m looking to output. The market event is going to dictate whether I’m looking for bid, mid, bid trade or ask (ph) data in Excel. For instance, I’m going to select my market event to be trade, and I would like to obtain the closing value of that one-minute intraday in Excel.
The next option in here is going to allow me to select a data – a start date, an end date, and a time frame that I’m looking to obtain the intraday data for. I will leave the defaults as they are to be able to obtain the data from 9 a.m. this morning until the current time. The interval size dictates the intervals of the bars or the output of the data in terms of minutes.
The real-time bar option will allow me to obtain when checked on enable a new data set every one minute when the market is open. I’m going to hit next once I’ve selected all my options. This will allow me to follow the DPDF settings for capital changes as I see on the terminal, or customize them for this particular data set.
I’m going to hit next, and this – I’m back to my preview page where it shows me what my output of my data will look like. When I finally hit finish, this is going to generate the data set for me on an intraday basis whereby I’m looking at the trading last price every one minute for IBM US equity. This complete the intraday bar wizard in Excel and obtaining intraday data on a one-minute granularity level within API.
Our final option in the data wizard is the historical intraday ticks. The historical intraday tick wizard will allow you to obtain historical tick raw data as it’s feed in by the exchange at a one-second granularity. This option also allows you to replicate the QR or the QRM tools on the terminal, as well as the GIP charts that you view on the terminal page.
Once I click into the historical intraday tick wizard, I am now able to input my security that I’m looking to pull in data for. So let’s mix things up. I’m going to enter in CLA commodity as my input ticker and hit enter to obtain the historical intraday bid and ask values for the oil futures. I’m going to select next, and here I can specify the specific intraday action that I’m looking to output into Excel.
For instance, if I’m looking for the bid and the ask values intraday, I would check on the options ask and bid. I can customize my time frame here to provide a start date, a start time, an end date, and an end time. And the optional arguments on the bottom here allow me to select my output in terms of what I can view as the output. I will leave the defaults as they are and select next. And I am off to my last option where it is again showing me a preview of my data as I normally do. I will select finish, and it will generate the intraday raw data for me in Excel.
So what I’m looking at here as my output is the intraday bid and ask values for CLA commodity. This is showing me the date and the time of the trade, the kind of action that – that occurred, the price of that particular action, as well as the size of that trade, which sometimes is scaled by a scale of 100 depending on the security. This completes our session on the real-time/historical option on the Bloomberg ribbon which allows you to easily download data into Excel without having to write your own formulas.
We are now going to click into the equities screen option, which is the next option on the Bloomberg ribbon. When I click on the equity screening option, it brings up a panel which is pre-loaded with all the equity screens that I have created on the EQS screen on the terminal. It’s very easy to use in the sense that I can simply select an equity screen that I have created, and on the bottom it shows me the universe of criteria that I used to create that screen.
Finally, when I select execute, it would output the results of my equities screen into Excel. The advantage of using the Bloomberg equities screening option is that it is a dynamic tool whereby any securities that match my criteria would be included and excluded as I refresh my data set each day.
Next, we also have a financials/estimates, which is a fundamental analysis data wizard within Excel which allows you to obtain financial data for equity securities within Excel. This wizard is very similar to the historical end-of-day wizard within the real-time/historical data wizard that we just ran through.
Next we’re going to go over the technical analysis wizard within the Bloomberg menu. The technical analysis wizard allows you to obtain technical analysis data into Excel for either a single or a list of securities with a click of a mouse. I’m going to click into the technical analysis wizard, and it brings up a data wizard just the way the real-time/historical data wizard brought up.
We have three different options available within the technical analysis wizard. The real-time option, historical end-of-day, and the intraday option. For the purposes of this exercise, I’m going to go with the historical wizard. When I click into the wizard, this screen looks very familiar, as it does with the real-time/historical wizard, whereby it would ask me to insert a security. For instance, I would type in Apple and the auto complete feature will allow me to select the security I’m referring to. I can simply double click on the ticker to be able to add it to the right hand side under selected securities.
I’m going to hit next on the bottom right hand corner, and this allows me to view all the available technical studies that I may download data for in API through the technical analysis wizard. For the purposes of this exercise, I’m going to select the simple moving average and click add. Once I have selected the study, I can simply select next to move on to the next step.
The next step is going to allow me to customize the inputs of my study. In this case, I am calculating a simple moving average of the last price for Apple given a period of five days. I can customize this further to say that I would like to obtain the simple moving average over the last 50 days historically in Excel.
Once I have customized my inputs, I would then select next. And this option is going to allow me to select a date range, as well as a periodicity. For example, I can say I would like to obtain the weekly data starting from January 1, 2011, until the current day. Once I’ve customized these options, I can then select next, and it brings me to a defaults page where I – again, I can standardize my data just the way I did in the historical wizards – in the real-time/historical wizards.
I’m going to leave the settings as they are and click next. And now I have the option of changing my capital changes defaults as they impact the price of the stock. I would like to follow my terminal settings whereby I don’t have any discrepancies. I’m going to click next, and I’m up to my preview page where it is going to show me a preview of my data set.
Finally, when I hit finish, it is going to populate the 50-day moving average for Apple over a weekly periodicity starting from January 1, 2011, until today. This concludes the basic API portion of the Bloomberg Essentials Online Training Program.
I would now like to go over some resources that you as a user have available within the Bloomberg toolbar, as well as on the Bloomberg terminal, as it pertains to API. One of the biggest resources that most of our clients use is the template library. That is located on the top left hand corner of the Bloomberg toolbar within the Excel workbook. If you click on the template library, you will notice that it is broken up by different types of markets. You have equities, fixed income, commodities, and so forth and so on.
As a user, you can also search for a template by clicking into the search box on the top left hand corner and type in your keyword. For instance, if I’m looking for financial statement data for an equity ticker I can simply type in the terms financial and hit the enter or the go key. This will allow me to obtain a template that meets my keyword search whereby I can simply click open and it opens up the template that is already pre-built for you as a user.
I can easily come in here and update the ticker of the template whereby all the data is automatically linked to the ticker of the template that I’m looking at. For example, I can change the IBM US to Apple US and hit the enter key, and it will update the entire template with the data for the company Apple.
The same template library is also available on the Bloomberg terminal on the XLTP tool. If you type in XLTP on the Bloomberg terminal, you also have access to the same template library as you do via the Bloomberg ribbon within Excel.
Another resource that you have available when you are trying to download data using Bloomberg is the help contents option on the top right hand corner of the Bloomberg toolbar. If you click on the help contents option, this is going to give you a list of Bloomberg API help content, and it’s a help page that is specific to the Bloomberg API.
The search future on the help contents option is a very useful tool when searching on how to obtain certain type of data into Excel. For example, if I search for the term historical and hit the enter key, it is going to give me a array – step-by-step directions on how to obtain historical end-of-day data into Excel using steps 1 through 6, and all of the different optional arguments that are available when downloading this data.
You always have the option of using the live help. The live help is a 24-hours a day, seven days a week service available to you from the analytics department here at Bloomberg. Finally, the last resource that you also have available on the terminal is our desktop API home page, which is located on DAPI.
When you type in DAPI and hit the enter or the go key, this brings you to the Bloomberg API home page. On this home page, you can find useful information such as new product releases, general frequently asked questions, as well as the link to sample spreadsheets, which is the Excel TP template library, and any seminars or sessions that are available – training sessions are available within your local region.
This concludes the basic API of the Bloomberg Essentials Online Training Program. Thank you.
Posts
0 comments