HistoricalPrices Add-in Help
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
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
- Download the Add-in .xlam file and save a copy in a safe location
- Start Excel, select File -> Options
- Select Add-ins -> Manage Excel Add-ins -> Go (Fig. 1)
- Select Browse then navigate to the downloaded file and Press OK. (Fig.2)
- HistoricalPrices should show up on the list.
- To uninstall in the future, just uncheck the box and delete the file.
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.
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.
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.
Clicking Refresh Sheet menu item will cause a refresh of all the HistoricalPrice data blocks on the currently selected worksheet
All HistoricalPrices data in the workbook is refreshed. If part of a Historical Prices data block is selected, it will be refreshed.
Refreshes all workbooks
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
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:-
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.
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:-
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.
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".
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.
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".
For prices, there are 6 fields available:
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)
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.
Using HistoricalPrices with VBA
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 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.
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
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.