Yahoo data - daylight saving time (DST) date adjustment


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


xlf-dst-difference
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.


xlf-dst-range-and-formula
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).


xlf-bhp-mar2019-corrected
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

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

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

Assigning DST switch routines to Defined Names


2.2 Correct mapping


Using VLOOKUP ref set to Dte - Dte_Adjust (figure 5), the trading dates and DST data are now aligned.


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


References