|
Bloomberg Subscribers: Build your own worksheet model to pull in real time and historical data
in one step
Assigning names to ranges
We used Bloomberg’s table wizard to retrieve the real-time data and used
Bloomberg’s history wizard to retrieve a year’s worth of historical data. We
subtracted 1 from Excel’s TODAY() function. The calculation in this cell we
named enddate (cell Q4). We subtracted 367 from Excel’s TODAY() function
and named the calculation in this cell startdate (cell Q3).
| Range name |
Cell |
Cell contents (Formula) |
| Symbol |
C3 |
INDU Index |
| Title |
B5 |
=BLP(Symbol,$Q$2,,,BLP|M!,,,INDU
Index,[NAME],,,) |
| RealTimeRange |
C8:G8 |
=BLP(Symbol,Fields,,,BLP|M!,,,INDU
Index,[OPEN,HIGH,LOW,LAST PRICE,VOLUME],,,) |
| HistoricalRange |
B9 |
=blph(Symbol,Fields,startdate,enddate,,TRUE,,,,,261,6) |
| Net Change Calculation |
H8 |
=F8-F9 |
| RSI Calculation |
I8 |
=100-(100/(1+(SUMIF(H8:H21,">0")/14)/((SUMIF(H8:H21,"<0")*-1)/14))) |
| Current Date |
B8 |
=TODAY() |
As you can see from the formulas, Bloomberg’s BLP and BLPH functions allow dynamic
cell referencing. This feature lets you name ranges for the symbol and the
fields and enter these names in the functions. When a value in the named range
changes, the functions update automatically. As a result no macrocode is
necessary to perform the update.
|
