smfPricesBetween() -- Documentation

Documentation for smfPricesBetween() function

Description

Summarizes historical stock quotes for a range of data from the Yahoo historical quotes interface.

Syntax

=smfPricesBetween( Ticker, Start Date, End Date, [Fields] )

Parameters

Ticker	=	Ticker symbol (e.g "MMM"). You can also use a special value of "Header" to return a row of column headings for the returned data items.
Start Date	=	Date to use as the starting date for the historical quotes date range; must be an EXCEL serial date value (e.g. NOW() or DATE(2006,10,15)).
End Date	=	Date to use as the ending date for the historical quotes date range; must be an EXCEL serial date value (e.g. TODAY() or DATE(2007,10,15)).
Fields	=	Which of the 10 possible fields to return; optional; default is to return all 10 fields (i.e. "01020304050607080910"). The 10 fields are: 01 = Open Date 02 = Open Price 03 = High Date 04 = High Price 05 = Low Date 06 = Low Price 07 = Close Date 08 = Close Price 09 = Volume 10 = Previous Close Note that each MUST be specified as a 2-digit number. So, if all you wanted was the "Previous Close" and the "Close Price", you would ask for fields "1008".

Examples

This would return the ten default data items for ticker symbol IBM for the past year:

=smfPricesBetween("IBM",TODAY()-365,TODAY())

If you array-entered the above formula over a 1-row by 10-column range, you would get the O/H/L/C/V/PC data items and dates for the various items.
If you wanted to know the 2007 YTD change for ticker "IBM", you could use:

=smfPricesBetween("IBM",DATE(2007,1,1),DATE(2007,12,31))

For example, using that and =smfPricesBetween("Header") on the line above it to get the headers, here's what the results looked like:

Open Date Open Price High Date High Price Low Date Low Price Close Date Close Price Volume Previous Close

2007-01-03 $80.51 2007-10-11 $121.46 2007-07-18 $71.46 2007-10-15 $118.03 3043106100 $80.27
Suppose in the above example, all I was interested in was the close prior to the starting date and the latest close of the period -- I could then have used these two formula:

=smfPricesBetween("Header",,,"1008")
=smfPricesBetween("IBM",DATE(2007,1,1),DATE(2007,12,31),"1008")

...which would have returned:

Previous Close Close Price

$80.27 $118.03
...more to come

Open Date	Open Price	High Date	High Price	Low Date	Low Price	Close Date	Close Price	Volume	Previous Close
2007-01-03	$80.51	2007-10-11	$121.46	2007-07-18	$71.46	2007-10-15	$118.03	3043106100	$80.27

Previous Close	Close Price
$80.27	$118.03

Usage notes

In most cases, this will need to be an array-entered formula. To array-enter a formula in EXCEL, first highlight the range of cells where you would like the returned data to appear -- the number of rows for the range should be the number of periods of data you are requesting from the function, while the number of columns for the range should be the number of data items you are requesting for each date from the function. Next, enter your formula and then press Ctrl-Shift-Enter.
In most cases, you will probably want to use cell references for the values. For example:

=smfPricesBetween(D17,E17,F17,"1008")

...where cell D17 contains the ticker symbol and cells E17 and F17 contain appropriate date values.
...more to come