|
How to extract real-time prices from Reuters page data
Once your real-time page data is retrieved and organized as shown, you can easily
use Excel’s MID function to extract any number or group of numbers from a line
of page-based text. Follow these steps:
- Use the List Tool to retrieve the entire
page of real-time data into a single column.
Reuters displays page-based information in a code-field format
that is similar to the way it displays real-time quote information.
The instrument code is the name of the page e.g. LIBOR01. The
fields represent the lines of each page. Some pages have 14 lines
of text (MONROW 1-14) and others have 25 lines of text (IRGROW
1-25). The maximum number of characters in line of Reuters page
text is 80. For example if you were to click on cell C15 in the
above example you would see the formula:
| =REUTER|IDN!'LIBOR01,IRGROW 11' |
The cell displays the text for the 11th line of the
LIBOR01 page. In order to have the columns of numbers line up,
format the cells which contain the page data with a non-proportional
font such as Courier.
- Use Excel’s MID function to parse the
information from the line of page text into a single cell.
The MID function is one of Excel’s text functions which returns
a number of characters from a text string. It has 3 parameters:
- The Text String or the cell
reference that contains the text string.
- The Start Number, which is
the location on the line of text where you want to begin parsing.
- The Number of Characters returned
from the text.
For example, if cell A1 contained the text “abcdefghjikl”
and you entered the formula MID(A1, 3,3) in cell B2, then
cde would be displayed in cell B2. The MID function
would start at the 3rd character and display three
characters. In our example, the selected cell (D15) contains the
formula =MID($C15,D$3,D$4). It returns the number 6.75813. Cell
C15 is the first argument for the MID function and contains the
real-time line of page text. The other two arguments are references
to cells, which contain the start number and the number of characters
to be returned. In our example, the start position is in cell
D3 and the number of characters to be returned is in cell D4.
You need to experiment a bit with the numbers used for the start
position and number of characters. Although it is not necessary,
we entered a series of numbers in cell C2 to help you find the
start position on the line of page text.
- Use cell references as arguments in
the MID function. Using this technique we can easily copy
the formula down to extract all the numbers in a particular column
of page data and we also have the flexibility to adjust easily
if the contributor shifts the position of the number on the line
of text. In this case, the MID function parses the number beginning
at the 10th position (D$3) on the line and displays
7 (D$4) characters. If you only want the number to display 3 decimals
rather than 5, all you need to do is change the value in cell
D$4 from 7 to 5. Changing a number in a cell is a lot easier than
editing numerous formulas. If you want to continue parsing data
you can copy the formulas containing the MID function to the adjoining
column. In our example we were easily able to parse the Libor
rates for the Euro by copying the formulas in column D to column
E and changing the start position and number of character settings
to 65 and 7.
- Name the cell containing the data.
The simple act of naming a cell can prove to be a great time saver.
For example, if you have extracted the 3-month Libor for the US
Dollar into a cell D15 on a worksheet, you should name the cell
US3mlibor. When you begin using this information within
financial formulas you just need to reference US3mlibor rather
than the cell reference, Pages!D15. This greatly simplifies the
task of manipulating page data within complex financial formulas.
- makes complex formulas a lot easier
to understand
- makes it a great deal easier to follow
the logic and flow of your model
- makes it easier to find and fix errors
|
