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).

Fig 1: ASX trading dates and Yahoo data dates comparison - ASX trading dates (column 1), Yahoo data dates (column 2) showing a 1 day lag for Yahoo data during the Australian DST period. Source: Trading dates - Summary WS, and Data dates - BHPfromJan17 WS

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.

Fig 2: DST start and end dates - in a format suitable for use with the VLOOKUP function - in ascending order on column B of the WorkArea WS

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 (--).

Fig 3: Formula development - WorkArea WS - 1. DST switch (0,1) testing 2. Trading date reference (ref) suitable for use in Defined Name, and 3. ASX trading date to Yahoo data date testing - a combination of 1 and 2

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

Fig 4: Name Manager view of - GetData; Dte_Adjust, Dte_2, and GetData_2

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.

Fig 5: Corrected mapping - GetData (column B) and GetData_2 (Column C). Match data are verified to alternate data base from Macquarie (inset)

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)