10
$\begingroup$

I'm currently working on some matlab code that is supposed to check a stock database for any errors (missing values, wrong values, etc.). The reason for this is that after reading this post I came to the conclusion that I'll probably have to write some data cleaning code in order to get accurate and reliable results when backtesting with this database.

The database has been downloaded from yahoo finance and contains the following columns for each stock: Date, Open, High, Low, Close, Volume, AdjClose.

So far the program scans for the following trivial errors:

  • Close > High
  • Close < Low
  • Open > High
  • Open < Low
  • High < Low

The program also checks if any of the data columns contains values less than zero or NaN.

What other errors/flaws could I look for in the database?

$\endgroup$
8
  • $\begingroup$ It will help if you explain the reasoning behind these checks. Why do you expect such errors to appear? $\endgroup$
    – user2092
    Mar 15, 2013 at 1:25
  • $\begingroup$ @EugeneS Data "scrubbing" is a common ritual in this industry. $\endgroup$ Mar 15, 2013 at 1:47
  • 2
    $\begingroup$ You could check for duplicate entries, you could make sure that your dates are all unique. $\endgroup$
    – Akavall
    Mar 15, 2013 at 2:58
  • 1
    $\begingroup$ @classifire There are lots of things to look for: missing days, prices that seem abnormal compared to previous ticks, adjustments that went wrong, etc. $\endgroup$ Mar 15, 2013 at 11:19
  • 1
    $\begingroup$ @classifire Via an external data source, which this guy didn't seem to understand. $\endgroup$ Mar 15, 2013 at 12:53

3 Answers 3

6
$\begingroup$

Few points from my experience:

1 Another filters that you that you should consider is for price = 999 or 999.99 that appears in some data providers.

2 Another set of checks is to look at cross-section of e.g. range = (high-low)/close over all names. Check for the smallest range and largest range to see if the values make sense. You can also check daily % change from one day to another. Check all largest moves for errors in the data. Flash crash in the US have created huge ranges, but if you see abnormal ranges on different days, check out the quality of data. Also September 2008 there are many nonsensical values even in very liquid products.

3 You have to be careful using yahoo (and other sources) for companies changing names, or going in / out of bankruptcy.

$\endgroup$
1
  • $\begingroup$ +1 Very good points - I'm definitely gonna add those checks. $\endgroup$
    – user2278
    Mar 17, 2013 at 9:49
2
$\begingroup$

The adjusted close will change after dividends and stock splits. So the old data will have to be replaced by the new. So it is usually a good idea to check for adj close of the downloaded values against current values.

I also like to check for downloaded data against some other source (like Google). I do this by writing a unit test that will randomly pick a date and download the data from Google and check against Yahoo's.

$\endgroup$
0
$\begingroup$

What you're talking about is sanity checks and if you really have to do those, it's good grounds to classify the data source as unreliable (or, alternatively, your understanding of said data source is inadequate). Typically, when getting from Yahoo, you shouldn't need to do this.

$\endgroup$
2
  • 4
    $\begingroup$ And yet this question clearly shows trouble with Yahoo's data. $\endgroup$ Mar 17, 2013 at 1:12
  • $\begingroup$ I partially agree with both of you: I just ran the code cleaning algorithm on my stock database downloaded from Yahoo (258 stocks for about 10 years each). It didn't detect any of those trivial errors mentioned in my question (e.g. close > high, etc.) - which supports Dmitri's answer. But I also agree with chrisaycock: There are a couple of posts indicating other serious issues with Yahoo's data (e.g. this one). $\endgroup$
    – user2278
    Mar 18, 2013 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.