Restart to return home

Historical Equity Data Module:  International Securities  

This module constructs a historical data base of international prices in Excel automatically from the web.  You specify the tickers, the date, the data frequency (daily, weekly or monthly) and the Historical Equity Data Module will create your spreadsheet file in the correct format for the Portfolio Analysis module for US and International stocks and indices.  It retains the currency of where the security is listed.

Tip:  If you are using this same set of securities in the FTS Real Time Position Manager you can specify the currency and retrieve the current exchange rate, so that all securities are marked to market in the currency of your choice at any point in time.

Overview

To learn how to use this data retrieval module, complete the following steps.  There are three main tasks:

First, you need to create a blank Excel workbook that contains ticker information.  For example, click on Excel Example.  In this spreadsheet we retrieve data for Yahoo, Royal Bank Canada, Deutsche Telekom, Cadbury Schweppe, Modelo C, Australia and New Zealand Bank (ANZ)  to provide an example of an international flavor of stocks.  For a complete list of supported international exchanges click on International Exchange Codes.

Second, you need to specify data frequency and starting, ending dates.  Tip:  Sometimes give data retrieval issue it is advised to specify a month or two earlier than you really want in case the first month fails to come in.

Third, you must parse the data extracted from the web into a form that the Portfolio module can read.

Specific Steps

Step 1:  Open a new Excel workbook e.g., Excel Example.  Once opened save this blank workbook by the name of your choice.  (Important Note it is best to save this example spreadsheet as a separate worksheet and then re-open in Excel to avoid it being automatically opened in Internet Explorer.  This does not have all the functionality required and can create unpredictable results).

Step 2:  Enter the tickers in row 1 (or the row of your choice) placing one ticker in each column.  The tickers should be exchange symbol.  The example spreadsheet provides a small sample of international stocks.  If you need help on tickers click on Getting Tickers.  Click on International Exchange Codes to see the international exchange codes that you use in the spreadsheet.  For example,  suppose you want the Canadian Pacific Railway the code is: CP.TO (it is listed on the Toronto Stock Exchange).

Step 3:  Give the focus to the Web Data Module and click on the button "Find Excel Worksheet."   Next, select the sheet you want to store the historical data in which must be the same sheet that you have placed the ticker symbols (e.g., Sheet 1).  Next complete the three text boxes starting with "Tickers are in Row."   That is, you must enter the row number for tickers (current example 1), and the starting and ending column letter. 

Finally, click on the button "Get Tickers."  If you have linked to the correct spreadsheet in this step you should now see a drop down menu that contains all of your ticker symbols.  

If you have made any errors you can click on the Clear Tickers button and repeat this or the appropriate earlier step.

Step 4:  The objective of this step is to specify the dates and the time period for the data.  Once specified click on the button Get Data.

Step 5:  If the data is of a standard form you can now click on the button Parse Data. 

Note:  If any price fails to come in for any reason (i.e., missing on the datafile) an error message will pop up, just click on OK to continue.

You will see the Date and Price data listed in the grid.  You will notice that the date is from recent to oldest whereas the module requires Oldest to Recent.  To correct this click on Sort Data.

Finally, click on Export to Excel.  You will get a Prompt for overwriting data in the spread sheet.  If you have selected the correct sheet click OK and now you spreadsheet is filled with the historical data.

Before using your data you should do a visual inspection of your spreadsheet database to check for any missing data.

Tip:  Sometimes the first row is missing for a particular security.  Try re-specifying from a couple of months earlier to overcome this if you know the security was traded at that time.  This can happen for various reasons when retrieving data and is outside of the control of the data module.  Retrieving data is quick and easy so you experimenting to see how far back the data goes for a particular security is a simple task.  Clearly monthly data spans a longer period of time than does daily.  Weekly is in between.  Once you become comfortable with the module there are some additional manual tricks you can use.  However, usually there is no need for this next step.

What if Step 5 Fails to Parse the Data Correctly?

If you have specified securities that have a non-standard format the Web Data Module lets you set the parsing criteria manually.

To do this click on the button "View first part of the first ticker's data."  Now if the headers do not match i.e., the date header, the price data header and the character used as a separator then you can manually enter these characters/text descriptors.   Normally, you will not perform this step.

OS Financial Trading System, PO Box 11356, Pittsburgh, PA 15238 USA, Phone 1-800-967-9897, Fax 1-412-967-5958, Email fts@ftsweb.com,