AmiBroker Documentation Item No. ab302.html

This is the part of AmiBroker documentation. Copyright ©2000 Tomasz Janeczko. All rights reserved.
THIS DOCUMENT IS OUTDATED. REFER TO AMIBROKER USER'S GUIDE FOR MORE UP TO DATE INFORMATION

HOWTO: Import quotes from Yahoo's site in CSV format

Introduction

Yahoo provides historical quotes in spreadsheet (CSV - comma separated values) format which can be easily imported into AmiBroker using its scripting features. This document will show you how to write and use the script which performs the importation. We will use JScript as a language and OLE Automation interface provided by Windows version of AmiBroker. You will need AmiBroker/Win32 version 3.2 or higher and Windows Scripting Host (WSH) installed on your computer. WSH is integrated into Windows 98, Windows 2000 Professional, and Windows 2000 Server. It is also available as a free component for Windows 95 and NT4.0 (you can download it from http://msdn.microsoft.com/scripting/windowshost/default.htm )

CSV Format description

The format of CSV file is very simple. First line contains fields description. It looks as follows:

Date,Open,High,Low,Close,Volume

This line means that the data lines will contain six comma-separated fields. First field will hold the date. The remaining fields will hold open, high, low, close prices and volume. All remaining lines hold just comma-separated data.

Here is an example showing four first lines from such a file:

Date,Open,High,Low,Close,Volume
1-Feb- 0,104,105,100,100.25,2839600
31-Jan- 0,101,103.875,94.50,103.75,6265000
28-Jan- 0,108.1875,110.875,100.625,101.625,3779900

Since there is no ticker name stored in this file format we assume that base part (without extension) of the file name holds ticker name. So file holding quotations of Apple should have name "AAPL.CSV". You should take care of naming the files correctly.

How to download quotes in CSV format from Yahoo's site

In order to get the quotes from Yahoo, please browse to its historical quotes page. Then enter a ticker name and interesting time period. Then you will see the table with quotation data. Below this table you will see "Download in spreadsheet format" link. Please follow it - and that's it - you just obtained data in CSV format. Please save the file with appropriate name (<ticker>.CSV).

The algorithm

In order to keep things simple we will not pay any attention to the first, format definition line. We will just assume that the file format is fixed. So, our algorithm will skip the first line then read the rest line by line. The contents of each line will be splitted into array of strings at each point where a colon occurred. Then the contents of the array will be interpreted as date, price and volume data and inserted into AmiBroker's database using its Automation objects.

Implementation

Here is JScript function that implements described algorithm:

function ImportCSV( filename )
{
	var fso, f, r;
	var ForReading = 1;
	var AmiBroker;
	var ticker;
	var date;
	var quote;
	var fields;
	var stock;

	/* Create AmiBroker app object */
	AmiBroker = new ActiveXObject( "Broker.Application" );

	/* ... and file system object */
	fso = new ActiveXObject( "Scripting.FileSystemObject" );

	/* we use file name ( without extension ) as a ticker name */
	ticker = fso.GetBaseName( filename ).toUpperCase();

    	/* add a ticker - this is safe operation, in case that	 */
    	/* ticker already exists, AmiBroker returns existing one */
	/* we are doing this outside loop since the file contains */
	/* quotes of single stock only */
    	stock = AmiBroker.Stocks.Add( ticker ); 

	/* open ASCII file */
	f = fso.OpenTextFile( filename, ForReading);

    	/* notify the user */
	WScript.Echo( "Importing " + ticker );

	/* skip first line which contains format definition */
	f.SkipLine(); 

	/* read the file line by line */
	while ( !f.AtEndOfStream )
	{  
		  r =  f.ReadLine();
		  
		  /* split the lines using comma as a separator */
		  fields = r.split(","); 
		  
		  /* split date at - separator */
		  var datefld = fields[ 0 ].split("-");

		  /* ensure Y2K compliance by converting year to 4 digit number */
		  var year = parseInt( datefld[ 2 ] );
		  year += ( year < 50 ) ? 2000 : 1900;
		  datefld[ 2 ] = year.toString();

		  /* put date back all together */
		  datefld.join(" ");

		  date = new Date( datefld );

		  /* add a new quotation */
		  quote = stock.Quotations.Add( date.getVarDate() );
		  
		  /* put data into it */
		  quote.Open = parseFloat( fields[ 1 ] );
		  quote.High  = parseFloat( fields[ 2 ] );
		  quote.Low   = parseFloat( fields[ 3 ] );
		  quote.Close = parseFloat( fields[ 4 ] );
		  quote.Volume = parseInt( fields[ 5 ] );
		  
	}

	/* refresh ticker list and windows */
	AmiBroker.RefreshAll();

	/* notify the user */
	WScript.Echo( "Finished" );

}

In order to import the quotations from the file just call this function with file name as an argument (note that JScript uses double backslash for representing (single) backslash in the path):

ImportCSV( "aapl.csv" );

You can find ready-to-use script file here along with some example data file.

Usage

First you must launch AmiBroker. Then you can run the script. WSH scripts can be run in two ways:

In the first case script output (WScript.Echo calls) is sent to console window, otherwise it is displayed in message boxes.

You can of course import multiple files by calling ImportCSV function multiple times within the script (changing the filename appropriatelly)

More information

For more information on using Windows Scripting Host, JScript and so on please check "Microsoft Windows Scripting Host: A Universal Scripting Host for Scripting Languages" article in MSDN.
For more information on AmiBroker's OLE Automation Objects please consult AmiBroker User's Guide.

Metastock is a trademark of Equis International, Inc. Microsoft, MS-DOS, Internet Explorer, MSDN, Windows, Windows NT, Win32, and Win32s are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries.