|
Reuters Subscribers: Build your own worksheet model to pull in real time and historical data
in one step
You can download the worksheet from this tip and be ready to go,
but if you want to dive into the techniques used to create it,
follow these steps:
- Use the Reuters Tools Quote tool to create
a real-time quote for the instrument you're interested in.
- Retrieve history for the same instrument
with the Reuters Tools History tool and place the table of historical
data immediately below the real-time date.
- Assign names to the ranges where you want
to place real-time, historical and other data.
- Create the calculations you want to perform
on the combined data.
- Apply formatting
- Open the Visual Basic Editor and write
an 8-line macro.
Assigning names to ranges
Good spreadsheet design includes naming ranges, especially if you
are going to reference cell ranges in macrocode. A quick way
to do this is to select the cell or range and then click in
the Cell Name box, which is immediately to the left of the formula
bar. Type the name you want to assign to the cell or range into
the box and press Enter. The table below shows you the formulas
we used and the names we used. These names are referenced
in the code later in the instructions.
| Range name |
Cell |
Cell contents (Formula) |
| Symbol |
C3 |
.DJI |
| Title |
B5 |
=REUTER|IDN!'.DJI,DISPLAY
NAME |
| RealTimeRange |
C8:G8 |
{=REUTER|IDN!'.DJI,[OPENING
PRICE,TODAY''S HIGH,TODAY''S LOW,LAST,VOL ACCUMULATED]'} |
| HistoricalRange |
B9 |
{=REUTER|TS1!'/RI=.DJI /PE=D
/EN=EARLIEST /EV=260 /FI=-600,19,12,13,6,32'} |
|
H8 |
=F8-F9 |
|
I8 |
=100-(100/(1+(SUMIF(H8:H21,">0")/14)/((SUMIF(H8:H21,"<0")*-1)/14))) |
|
B8 |
=TODAY() |
Writing the macro that runs the application
One of the most dramatic changes in Office 97 was the inclusion
of a full scale Visual Basic programming editor within all the Office
applications. (In Excel 97, a shortcut to access the Visual Basic
Editor is to simultaneously press Alt F11.) This powerful language
gave you the ability to tie your macrocode to a specific workbook
event. For example, you can have your macrocode run only when a
workbook is opened or a specific worksheet is selected. In our case,
we are going to include it in an event procedure, which will run
when the contents of a cell on the worksheet change. This is effected
with the Change event.
The Visual Basic Editor is divided into the Project
Explorer window, the Properties
Window and the Code window.
If your Visual Basic Editor does not look exactly as described,
you can select the windows to display by choosing them from the
View menu.
In the Properties Window, select shtRTHistory. There are two dropdown
boxes above the Code window. In the left dropdown box, select
Worksheet. Then, in the right dropdown box, choose
the Change event procedure. This action brings in the
first and last line of the macrocode for the procedure.
Type in the following lines of code between the first and last lines:
|
If Target.Address = shtRTHistory.Range("Symbol").Address Then |
|
sSymbol = .Range("Symbol").Value |
|
.Range("HistoricalRange").FormulaArray
= "=REUTER|TS1!'/RI=" & sSymbol & _
" /PE=D /EN=EARLIEST
/EV=260 /FI=-600,19,12,13,6,32'" |
|
.Range("RealTimeRange").FormulaArray
= "=REUTER|IDN!'" & sSymbol & _
",[OPENING PRICE,TODAY''S
HIGH,TODAY''S LOW,LAST,VOL ACCUMULATED]' " |
|
.Range("Title").Formula
= "=REUTER|IDN!'" & sSymbol & ",DISPLAY
NAME' " |
The Change procedure watches a cell, called the target.
In our case, we're waiting until the target is cell C3, the
cell we named Symbol. When the target cell matches our cell,
then the macro is run. The macrocode does nothing more than
take the value entered in cell C3 (Symbol) and assign it to
the variable sSymbol. The remainder of the code overwrites the
real-time and historical data formulas using the value in sSymbol
as the instrument code.
|
