xlf QandA series

Yahoo data - null fields



Answer icon Answer

Database NULL values usually mean that the data is missing, unknown, or not available. Yahoo historical data sometimes includes a valid date, but a series of fields containing null. An example is AXJO.AX (the S&P/ASX 200 index - an Australian stock index) as shown in figure 1, worksheet line 675 for 4 September 2017. Null fields are more frequent in stock price data prior to 2012, see figure 4.

Fig 1: AXJO.AX data - S&P/ASX200 index data with null on 4 September 2017

4 September 2017 was a trading day on the Australian Stock Exchange. See figure 2.

Fig 2: ASX trading day list - a filter criteria linked to a holiday array is applied to the weekday list in column B (advanced filter - List range)

MySQL describes null as a missing unknown value. Null is displayed without the string double-quote, see figure 3. A Null is different to 0 (zero) or an empty string ("", or '').

A Source Code view of Yahoo data is displayed in figure 3.

Fig 3: Firefox - View Page Source - entry for 4 September 2017 highlighted, with Unix date equivalent of 1504483200. "open":null, "high":null, …

When loaded to an Excel worksheet, the Yahoo Null is displayed as a text "null", figure 1.

How to handle the null fields For participants of excelatfinance.com related programs, the recommendation is:

  1. Count the number of nulls: COUNTIF(range, "null")
  2. If COUNTIF returns a value greater than 0
  3. Then replace null with string "#N/A" or formula =NA(). These statements are equivalent, figure 4.
    Fig 4: Find and Replace - Yahoo null with Excel N/A error

Other examples of Yahoo data null

Advance Nanotek Limited (ANO.AX) data for 2011 has numerous null records (see figure 5). A quick examination of ASX charts suggest that ANO stocks did not trade on these dates.

Fig 5: ANO data 2011 - Advance Nanotek Limited was listed on 24 February 2005.