Downloading historical price and market information
In this presentation I'll be showing you how to download historical price and market, or accounting information into Excel from Bloomberg. First thing to check is 1) that Bloomberg is open (the application) and secondly that you have this Bloomberg ribbon available. You'll see there are a number of different tools that you can use to download information into Excel, and we're going to focus on historical data. So at the top of this toolbar at the left, you'll see a button for real time or historical data. You click on that and you'll be choosing historical end of day data.
Now a wizard will pop up and this will allow you to select the securities that you'd like to download information on. So starting off let's choose BHP. So I'm typing BHP and it's coming up with a drop-down box and I can select the equity, security. I'm now going to choose a peer company, Rio Tinto. The Australian listing, AU. And I'm also going to choose information on the exchange, so ASX200. And double click on that like the others to bring that up. If I wanted to, I would also be able to pull up information on an entire index by selecting an index in the second half of the screen. For example if I said, S&P 500. Index on the drop down box.
And then it's letting me choose from securities. So for example I could then choose either all of them or individual companies that I wanted to look at. So I might just choose one company and then add that to the bottom. When I've finished with my company list I'm going to select next. Now I need to choose the data fields that I want to download. So I can do this in two ways, firstly I can choose or search for a field, by typing it in this search text book, for example price. And pressing enter. PX Last is typically the field you would use to download the end of day share price and I'm going to double click on that. But if I'm interested in finding accounting related fields, it may be worth going back into Bloomberg and searching under a page known as FLDS. Enter. Which will let me do a search for the data fields that are available for particular securities.
So if I put the security up here, BHP for example, and the data field that I'm after is earnings per share, and I press enter, it's going to give me a list of the data fields relating to earnings per share. There's actually 44 pages, but it's going to rank them by most used and most likely. So you'll see that the first few are all potentially relevant. The first one here says BEST EPS, and BEST when looking at Bloomberg always refers to consensus from analysts. If I want a full definition, I can click on the item and it will give me more information about this particular data field. To get back to the previous page I'll select the end menu key on the keyboard. So I can see these are a couple of items that I'm interested in. I'm going to go back now that I've seen they are the right items, back to Excel, and I'm going to search, do the same search, EPS. I'm going to select both BEST EPS by double clicking on that. I'm also going to download Trailing 12 months EPS by clicking on that, and you'll see the same definitions are in Excel itself, except it can be easier to navigate in the Bloomberg page.
So I'm happy with that, last price, consensus EPS or BEST EPS and Trailing 12 months EPS. I'm going to hit next to go to the next page. So this is when I'm selecting the period and range of data. The period can be selected at the top right. I can choose weekly, monthly, quarterly data. I'm going to choose semi-annual data to download for all these items. And now I can choose the date range. What I'm going to choose is either between a series of dates by doing a fixed time series or a relative time series when I might be looking at the last 10 semi-annual periods. I'm going to do the last 5 years of data, therefore it's going to be the last 10 semi-annual periods.
Okay, Next. So this is quite important. This is going to be determining what, how I want to treat days when the data isn't available for example, a weekend, a holiday, a trading halt. I would always recommend to select either all trading weekdays or all calendar days and then either carry over last value or select a blank field for when data is not available. Next. This is a selection of how you want to treat corporate actions. The recommended settings are typically recommended. And this is describing how it's going to be laid out. For example, is the data going to be orientated on the horizontal axis or the vertical axis. And whether you want to display the security and date fields. And finally, do you want this to be in reverse or chronological order. When you're happy with the settings, click finish. It's going to begin the process of downloading the data.
So now you'll see that it's downloaded the data sets that we're after. And you've got the information that we've requested. Now if you wanted to make changes to the data we've selected, all of the data that's been downloaded has been aggregated from this cell, the top date cell. The formula itself is obtained in this cell and the format it takes is =BDH(ticker, field or fields, start date, end date). Now these other arguments are all the other arguments that were used in the Excel wizard. If you want to change any of these or work out what they are and what they represent and what the overrides are, you can click on the fx button, click on to help on this function and you'll get a full description and information of the formulas, how they're represented and any of the fields that you're able to override.
What you'll also be able to do is, if there are any other arguments you wanted to modify, you can click on that argument and it'll give you the different selections that are available. So I'm going to leave it as is, because I'm happy with the data I've got there. And that's the best way to download historical data.
Bloomberg
