Transcript File

08. Data from
Bloomberg Formulas
Launch Bloomberg and Press GO
Enter your login details
Choose Settings Wheel, then set window size to Normal
From Windows Menu
Click Install Excel Add-In
Press Install
Click OK
Click Microsoft Excel 2010
File -> Open -> 08b-datastart.xlsx
When we know the Ticker symbol of an asset we can use
Bloomberg formulas to download data directly to Excel
Current Data: BDP
Bloomberg Data Point
Click on Bloomberg
Click on cell N1 and select Find Fields
Search for e.g. dividend
For variables without a calendar
we use a BDP formula
Select Dividend Ex Date and click Add
Click Apply and then Close
Type =BDP(Ticker,Datafield)
Copy cell and paste for all companies
Now have data for that variable
Historical Data: BDH
Bloomberg Data Historical
Specify particular dates
Click on cell O1 and select Find Fields
Search for e.g. dividend
For variables with a calendar we use a BDH formula,
which also needs a start and end date for the data
Select Dividends per Share and press Add
Click Apply then Close
Type =BDH(Ticker,Datafield,StartDate,EndDate)
Copy cell and paste for all companies
Now have data for all companies
Historical Data: BDH
Bloomberg Data Historical
Accounting Data
Balance sheet data is usually only available on a quarterly or
annual basis so we need to be careful about dates
Type the variable name in P1
e.g. TOT_DEBT_TO_TOT_ASSET
Use the same BDH formula as before but specify Financial Years as “FY2011”,
and request data per Financial Year “per=fy”
Copy and paste to get for all companies
Bulk Data: BDS
Bloomberg Data Sets
Some data is stored in a bulk data set
Click on cell Q1 and select Find Fields
Search Top Holder
Select Top 20 Holders and Add
to get largest shareholders in a company
Click Apply and Close
Extract first row using
=BDS(Ticker,Datafield,“StartRow=1”,“EndRow=1”)
Shows largest shareholder in this company, with the percentage
of shares which he owns
Challenge
• Calculate the current Dividend Yield for each
company
• Calculate the Total Assets for Financial Year
2011 for each company
Multiple rows of data
for one company
• All of the examples so far have been used to get one
piece of data at a time for a particular company
• The formulas can also be used to get data for
multiple time periods at once
Insert new sheet
If we want to find data for Google we type its
ticker symbol which is GOOG US Equity
Click on a blank cell and select Find fields
Search Price Earnings and select PE_RATIO
Select PE_RATIO and click Add
Click Apply and Close
Download P/E ratio every day between 2010 and 2012 using
BDH(Ticker,Datafield,StartDate,EndDate)
Gives a list with date and data
We may want Total Assets for every financial
year since 2007
Select a new cell and Find Fields
Search Total Assets
Select Total Assets and click Add
Click Apply and Close
Use formula
=BDH(Ticker,Datafield,StartDate,EndDate,per=fy)
Gives year-end Total Asset figures
We may want information
on all top 20 shareholders
Select a new cell and Find Fields
Search for Top Holders
Select Top 20 Holders and click Add
Click Apply and Close
Use formula
=BDS(Ticker,Datafield)
Outputs everything in dataset
about top shareholders
File -> Save As -> 08c-datacompleted.xlsx
Challenge
• Download information about Microsoft (MSFT
US Equity)
• Obtain dividend yield for every day between
2004 and 2012
• Find the total debt to asset ratio for each
financial year between 2009 and 2012