Yahoo data - daylight saving time (DST) date adjustment
This file includes material relating to the GetData formula used on the Summary WS of the Stock Analyser project.
0. Background
Australian stock price historical data sourced from Yahoo Finance has a systematic date error during the Australian daylight saving time period. The error first appeared at about the time Yahoo shut down its Stock Data API service in November 2017. Since then, Yahoo data record dates lag Australian Stock Exchange (ASX) Trading dates by one day in six monthly cycles.
Figure 1 compares of list of ASX Trading dates to a list of Data dates for BHP.AX stored as Yahoo historical data records. As an example of the one day lag, trading day data for 2 October 2017, the first day of the DST period, is indexed as 1 October 2017 on the historical data file (figure 1).
This module follows the familiar technique for stock market data, of a Summary WS with a list of trading days linked to Data ranges of for individual companies. Mapping the Summary WS to companies is performed with a WS VLOOKUP function.
The next section develops an adjustment to the VLOOKUP argument lookup_value for the Yahoo DST error.
1. Correcting the DST date error
1.1 List of DST start and end dates
Daylight saving dates for Melbourne are sourced from www.timeanddate.com. See figure 2 for the DST sample dates, 3 April 2016 to 6 October 2019, used in this development module. The dates are listed in ascending order. TRUE is start date of DST, and FALSE is the end date.
1.2 The DST switch routine
The DST switch procedure is item 1 in figure 3 (DST switch (0,1) testing).
- 1 Formula:
=--VLOOKUP($R5, DST, 2, TRUE)
Lookup the date 22-Sep-17 in the DST range, and return the approximate match from column 2: FALSE. Then convert the Boolean value FALSE to numeric equivalent (0) with the double negation operator (--).
- Formulas 2, and 3 are designed for assignment to defined names
- 2 Trading day ref Formula:
=INDIRECT("R" & ROW() & "C" & COLUMN(W:W), FALSE)
- 3 Trading day to Data date testing Formula (combining 1 and 2):
=INDIRECT("R" & ROW() & "C" & COLUMN(W:W),FALSE) -
(--VLOOKUP(INDIRECT("R" & ROW() & "C" & COLUMN(W:W), FALSE), DST, 2, TRUE))
2. The DST adjusted lookup procedure
2.1 Dte_Adjust defined name
The original lookup procedure is named GetData. Item ❸ in figure 4, with table_array as BHP_AX_1
Assigning DST switch routines to Defined Names
- Assign modified 3 to Defined Name Dte_2 ❶
- Assign modified
(--VLOOKUP(INDIRECT("R" & ROW() & "C" & COLUMN(W:W), FALSE), DST, 2, TRUE))
to Defined Name Dte_Adjust ❷
2.2 Correct mapping
Using VLOOKUP ref set to Dte - Dte_Adjust
(figure 5), the trading dates and DST data are now aligned.
References
- ExcelAtFinance (2018), UDF named YDate Accessed: 11 June2019
- Rigopoulos I (2018), Yahoo Finance Live Feeds in Excel after their API Discontinuation in November 2017 Accessed: 11 June2019
- Download the Excel file for this module: xlf-dst-dates-v2.xlsx [82 KB]
- Download the Excel file for the UDF named YDate module: xlf-ydate-v2.xlsm [35 KB]
- Development platform: Excel (64 bit) Office 365 ProPlus
- Published: 11 June 2019
- Revised: Friday 24th of February 2023 - 03:12 PM, Pacific Time (PT)