smfGetYahooHistory() -- Documentation

Documentation for smfGetYahooHistory() function

Description

Returns historical stock quotes (and limited options history) from Yahoo

Syntax

=smfGetYahooHistory( Ticker, [Start Date], [End Date], [Period], [Items], [Headers], [Resort], [Rows], [Columns] )

Parameters

Ticker	=	Ticker symbol (e.g "MMM")
Start Date	=	An optional starting date. The date can either be a string value (e.g. "mm/dd/yyyy") or an EXCEL serial date value. If none is specifed, the default is "1/1/1970".
End Date	=	An optional ending date. The date can either be a string value (e.g. "mm/dd/yyyy") or an EXCEL serial date value. If none is specifed, the default is tomorrow's date.
Period	=	An optional character indicating the type of data period to retrieve. The default value is "d". Possible values: "d" = Daily quotes "w" = Weekly quotes (Yahoo chooses to use the first trading day of the week as the end of the period) "m" = Monthly quotes (Yahoo chooses to use the first trading day of the month as the end of the period) "q" = Quarterly quotes (Yahoo chooses to use the first trading day of the month as the end of the period) "s" = Semi-annual quotes (Yahoo chooses to use the first trading day of the month as the end of the period) "a" = Annual quotes (Yahoo chooses to use the first trading day of the month as the end of the period) "v" = Dividend history ("T" is the only "Item" that makes a difference - Date and Dividend amount are automatic)
Items	=	An optional character string indicating which data items to retrieve. The default value is "DOHLCVU". Possible values within the character string: "T" = Ticker symbol "D" = Ending date of the period "O" = Opening price of the period "H" = High price for the period "L" = Low price for the period "C" = Adjusted closing price for the period "V" = Trading volume for the period "U" = Unadjusted closing price for the period "X" = Amount of dividends paid in period "S" = Text indication of split amount
Headers	=	An optional binary value to indicate whether headers should be returned for the column(s) of data. The value defaults to 1. Possible values: 0 = Suppress column headings 1 = Display column headings
Resort	=	An optional binary value to indicate whether data should be resorted or not. The value defaults to 0. Possible Values 0 = Do not resort data; present raw data as returned from Yahoo!; newest data will be on the top rows of table. 1 = Reverse the order of data returned from Yahoo!; oldest data will be on the top rows of table.
Rows	=	An optional integer value that can override the number of rows of desired output. This is typically not relevant, as it is automatically based on the number of rows you array-entered the formula over.
Columns	=	An optional interger that can override the number of columns of desired output. This is typically not relevant, as it is automatically based on the number of columns you array-entered the formula over.

Examples

This would return the twelve default data items for ticker symbol IBM:

=smfGetYahooHistory("IBM")

If you array-entered the above formula over a 10-row by11-column range, you would get the D/O/H/L/C/V/U/F/G/X/S data items for the latest 9 days of history.
This would return a single item -- the adjusted closing price for ticker symbol IBM for January 3rd, 2007:

=smfGetYahooHistory("IBM",,"1/3/2007",,"c",0)
This would return a single item -- the current 5-day moving average for ticker symbol MMM:

=AVERAGE(smfGetYahooHistory("MMM",,,,"c",0,,5,1))
If you array-entered the above formula over a 10-row by 6-column range, you would get the D/O/H/L/C/V data items for the latest 9 days of history. =smfGetYahooHistory(B4,"1/1/2019","2/28/2023","d")

You can also use an option formatted like this: SPXW240426P05305000

just like a ticker and it will give daily data for up to 4 weeks.

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.
If the function is array-entered over a range that is larger than the amount of data available from Yahoo, the excess portion of the range will be filled with blanks.
If the function is array-entered over a range that is smaller than the amount of data returned from Yahoo, the excess Yahoo data will simply be ignored. A user-defined function can only return data to a cell or a range that it has been given access to -- in this case, by the range used when array-entering the function.
...more to come