Yahoo data - daylight saving time (DST) date adjustment

The DST error in Yahoo data was resolved by Yahoo Finance in late 2019.

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

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.