xlf QandA series


Yahoo data - null fields


QUESTION

WHY DO SOME YAHOO PRICE RECORDS SHOW 'null'?


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.


xlf-axjo-null
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.


xlf-4-sep-17
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.


xlf-bs-multiple-options-implied-volatility-goal-seek
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.
    xlf-find-replace-null-with-na
    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.


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

References