HistoricalPrices Add-in Help

Video contents

0:40 Loading the add-in
1:40 Basic operation
2:30 Symbol by reference
3:17 Changing the range
4:35 Limits on data
5:00 Refresh buttons
7:00 Automatic refresh
7:20 Splits and dividends

8:30 Filtering nulls
9:35 Customizing fields
10:52 Headers
11:11 Multi-symbol table
15:00 UTC Offset
15:30 Date filtering
15:55 PriceOnDate function
17:06 Autoupdate a worksheet
19:00 Adding a reference (VBA)
19:55 Using with VBA

When you download the HistoricalPrices Add-in, you should Save the Add-in file, and not Open it. See how... ↓

How to load the Add-in

  1. Download the Add-in .xlam file, scan for viruses then save a copy in a trusted location
  2. The recommended location is "C:\HistoricalPrices Add-in"
  3. On newer Windows versions you may need to Unblock the file. Right click the filename and select "Properties". Under "General" in the "Security" section check "Unblock".
  4. Start Excel, select File -> Options
  5. Select Add-ins -> Manage Excel Add-ins -> Go (Fig. 1)
  6. Select Browse then navigate to the downloaded file and Press OK. (Fig.2)
  7. HistoricalPrices should show up on the list.
  8. To uninstall in the future, just uncheck the box and delete the file.

How to unblock the file

Fig1: Unblock the file by selecting the filename and right clicking, then select Properties. Under "General" properties check the Unblock box.

How to load the add-in part 1

Fig.2: From the File->Options->Add-ins menu select Manage Excel Add-ins

Loading the add-in part 2

Fig 3: Click on Browse, select the downloaded .xlam file and click OK. HistoricalPrices should appear in the list as checked.

The HistoricalPrices Add-in sub-Menu

The HistoricalPrices Add-in sub-menu on the ribbon.

This sub-menu will appear on the ribbon once the Add-in is loaded

Edit or Insert New HistoricalPrices

Shows the Edit or Insert New HistoricalPrices button


This button will cause the HistoricalPrices userform dialog to appear. If you have a HistoricalPrices User Defined Function selected on the worksheet, this will allow you to edit the parameters. If a HistoricalPrices User Defined Function is not selected, a new one will be created and inserted in the selected cell when OK is clicked. Any existing content in the selected cell will be over-written.

Refresh Buttons

Showing the refresh buttons on the sub-menu

The HistoricalData in the workbooks does not refresh when the worksheet or workbook is changed as this can slow down the spreadsheet and it can also lead to unnecessary polling of the data sources. Refresh is achieved by using the refresh buttons shown above, or by setting refresh to automatic causing a refresh every data interval.

Refresh Selection

If a HistoricalPrices User Defined Function is selected, the data associated with it will be refreshed. If part of a Historical Prices data block is selected, it will be refreshed.

Refresh Sheet

Clicking Refresh Sheet menu item will cause a refresh of all the HistoricalPrice data blocks on the currently selected worksheet

Refresh Workbook

All HistoricalPrices data in the workbook is refreshed. If part of a Historical Prices data block is selected, it will be refreshed.

Refresh All

Refreshes all workbooks

Refresh Button

You can add a refresh button to any page and move it to any position. The button will be remembered even after the workbook is closed. Clicking on the refresh button will refresh all HistoricalPrices on the current page, unless a HistoricalPrices dataset or UDF is selected, in which case only the selected HistoricalPrices will be refreshed

HistoricalPrices Dialog

The HistoricalPrices Dialog Form


This dialog can be activated either by selecting "Edit or Instert New Historical Prices" on the HistoricalPrices menu on the ribbon, or by right clicking when a cell is selected on the spreadsheet and selecting "Historical Prices" from the context menu.
When the dialog is filled out and the OK button is clicked, a User Defined Function (UDF) will be inserted into the selected cell, or the existing UDF edited and replaced. For example, for the above dialog the UDF would look something like this:-

=@PriceHistory(H5,"MSFT","Prices",1,"Years","1 day","Descending","Auto","Yes",,,"TOHLCV","Yes","Manual")

The "@" sign may not be present, depending on your version of Excel. The UDF can be edited either manually or (recommended) via the dialog.

Destination

How to set the destination cell of the Historical Prices data

Specify or click on the top left cell of the destination of the historical data. Can be on the same sheet, or a different sheet in the workbook.

Symbol

Setting the symbol for the Historical Prices data

Enter the Yahoo Symbol for the security which you wish to retreive historical prices for. You can enter a symbol or you can enter a cell reference, or just click on the cell that holds the symbol.

About Stock Symbols

You can enter any stock symbol recognized by Yahoo Finance, the spreadsheet will then fetch all the historic price data needed for analysis. If the symbol is not listed on the NYSE, NASDAQ or AMEX exchanges, you should follow the symbol with a dot, then the exchange code.

Examples of Symbols of different types

Examples of Symbols of different types


History Type

When you select the pulldown you will be given the choice of Prices, Splits, Dividends or ByRef. Select ByRef if the history type is inside a cell. You will then be prompted for the cell reference, or you can click on the cell which has the History Type.

Range

Specify the Range of the Historical Prices

Use the Range part of the dialog to specify the range (the total time span) of the historical data requested. There are two fields; the left-most is numeric and the right-most allows you to specify the units as minutes, hours, days, weeks, months or years. You can also select YTD (year to date) and Maximum. Maximum may give unpredictable results and intervals.

It is possible to request data beyond the limits of what is available from Yahoo, in which case there will be an error reported in the UDF cell. The data limits are approximately:-


Interval

Use the Interval setting in the dialog to specify the time span of each OHLC candle of the historical data requested. The interval can be set to 1, 2, 5 or 15 minutes, 1 hour, 1 day, 1 week, 1 month or 3 months. See Range for information on how much data can be requested. If refresh is set to Automatic, the interval is also the time between refreshes.

Date Order

Set the Date Order to "Ascending" or "Descending" using this dialog. Like all settings, this can also be referred to the spreadsheet.

UTC Offset

Data comes from the provider with timetags in UTC (Universal Time Code) which corresponds to GMT (Greenwich Mean Time) or London time. Use the UTC Offset setting in the dialog to specify the time difference between the displayed time and the actual timestamps. By default, this is set to "Auto" which causes the displayed time to be market time, based on metadata extracted from the data stream.  However, if you require another time offset (if you would like to display local time for instance) you can set an offset here in hours. The value should be between -14 and +12. To revert to "Auto", click on the field and select "Cancel".

Filter Nulls

When securities are thinly traded, there can be intervals where no price is set because no trading occurs. The provider returns null prices for these intervals. In this example, with Filter Nulls set to Off, a security NAZ shows no trades for the 15:56 interval:

Setting Filter Nulls to "Yes" will fill the null periods with prices interpolated from the close price of the previous interval. In this example, if Filter Nulls is set to Yes, the data would present like this:

The 13.455 price from the 15:54 interval close is applied across the 15:56 interval, and the Volume set to -1 to indicate an interpolated interval. This processing can improve in both the graphing and numerical analysis of the data.

Start Date and End Date

You can filter out data before and after a specified date and time by entering values in the Start Date and End Date fields. When doing this, ensure that the Range is set appropriately to encompass the Start Date. You can include a time, for example a Start Date of  "7/17/2020 15:43" would only show data after the given date and time. To clear these fields, click on them and select "Cancel".

Fields

For prices, there are 6 fields available:

T   Timestamp
O  Open
H  High
L   Low
C   Close
V   Volume
A   Adjusted Close (intervals one day and greater only)

You can have as many of these fields as you like in the output, and in any order. Use the letters shown above in the order you wish. For example:

TCO:              Timestamp, Close, Open
TOHLCVA: Timestamp, Open, High, Low, Close, Volume, Adj Close
C:                    Close price only (no timestamps)

All prices are adjusted for splits, but adjusted close prices are adjusted for both splits and dividends. Fields for splits and dividends are not adjustable.

Headers

Headers can be removed from the data by setting the Headers dialog to No.

Refresh

For each block of historical data, refresh can be set to Manual, Automatic or If Selected.

Manual refresh is achieved using either the menu refresh buttons, the optional worksheet Refresh Button or by clicking OK on the HistoricalPrices dialog.

Automatic refresh will refresh automatically every interval. For example if the interval is set to one hour, the data will refresh every hour. However, automatic refresh out of market hours is minimal to prevent polling the same data over and over.

If Selected refresh is designed for historical prices which you wish to update infrequently. These blocks will only refresh if you select part of the data and click on refresh. If you never wish to refresh the data, one way is to erase the UDF associated with it.

The PriceOnDate function

PriceOnDate function gives price on any date

The PriceOnDate function takes up to three arguments.

=PriceOnDate(Symbol, Optional Date, Optional Type)

Symbol: a valid Yahoo symbol

Date:      If not supplied, a recent price is given.

Type:      daily "Open","High", "Low", "Close", "Volume", or (default) "AdjClose"

Using HistoricalPrices with VBA

Add a Reference to the Add-in

To use the VBA functions inside the HistoricalPrices Add-in in your own VBA code, you must first add a Reference to the Add-in. This will expose the functions so that you can use them.
1. Click on Developer->Visual Basic->Tools->References
2. Check the box next to HistoricalPrices_Add-in

Note, if the Add-in is not yet loaded, you can Browse to the .xlam file in order to add the reference. This will also load the Add-in

The HP_GetYahooRequest function

The HP_GetYahooRequest function is the core function for getting prices from the JSON stream. It will write the price data to the spreadsheet at the location requested, or return an error string. The return value is a null string if successful, or a possibly meaningful error message. The arguments are as detailed below:

DestinationRange As Range

The top-left cell of the data e.g. DestinationRange = ActiveSheet.Range("D7")

FieldsToExtract As Variant

A variant array of JSON keys detailing the fields to extract, in the desired order e.g.  FieldsToExtract = Array("timestamp", "open", "high", "low", "close", "volume", "adjclose")

StockSymbol As String

The Yahoo symbol of the history requested (see above)

RangePart As String

The overall time range of the data measuring backwards from the present. Can max, ytd, Xy, Xm, Xd, Xh, Xm where X is an integer, e.g. "50d"

IntervalPart As String

The time interval of each data bar:  1m, 2m, 5m, 15m, 1h, 1d, 1mo, 3mo

Optional DateOrder As Integer = xlDescending

The date order of the data, can be xlDescending or xlAscending

Optional TOffset As Double = -1000

A time offset applied to the JSON timestamps. See UTC offset above. If set to -1000, the program will try to determine the offset from the metadata and should result in the timestamps being shown in market time.

Optional FilterBadPrices As Boolean = False

Set to True to interpolate prices across "null" periods. See Filter Nulls above

Optional StartingDate As Date = 0

Data with dates before StartingDate will not be written to the spreadsheet. If zero, no StartingDate filtering is performed.

Optional EndingDate As Date = 0

Data with dates after EndingDate will not be written to the spreadsheet. If zero, no endingDate filtering is performed.

Optional Headers As Boolean = True

If False, headers are removed

Optional StrJSONResponse As String = ""

After the function is called, this string will contain the text of the JSON response from the website.

The HP_EmulateURL Function

HP_EmulateURL emulates the old Yahoo ichart URLs which were supplanted by the JSON interfaces and closed down around May 2017. These URLs provided a Comma Separated Value response. If your program uses URLs like
http://ichart.finance.yahoo.com/table.csv?s=MSFT&a=05&b=06&c=2000&d=05&e=06&f=2017&g=w
then you can use HP_EmulateURL to emulate these URLs so that changes to your code will be minimal. This interfaces can be used for daily, weekly or monthly prices, splits and dividends. The ichart URL is the exact same URL that you were using up until Yahoo! turned off the service in May 2017 so changes to your code will be minimal. The URL itself has many arguments depending if you want daily prices (&g=d), weekly prices (&g=w), monthly prices (&g=m), dividends (&g=v) or splits (&g=s). Start date is given in the a,b and c parameters, end date is given in the d,e and f parameters. The stock symbol (&s) must be understood by Yahoo!Returns a null string if no errors found, otherwise returns a possibly meaningful error message. Takes the following parameters

InputURL As String

An ichart.finance.yahoo.com URL

DestinationCell As Range, _

A single cell which will be the top-left most cell of the historical data

Optional CSVCell As Range = Nothing

The top-left-most cell where the CSV data should be written.

Optional DateOrder As Integer = xlDescending

The date order, xlDescending or xlAscending

Optional SkipBadRows As Boolean = True

If rows come back with nulls (usually because no trades occurred in an interval) HP_EmulateURL will filter these out if SkipBadRows is set to True.

Optional ShowRawData As Boolean = False

Shows the raw data

Optional ShowHeaders As Boolean = True

If False, suppresses writing of the headers.