So I am working on an algorithm that fetches data from an external CSV I have hosted on Google drive. However, on a few random days for different securities, the algorithm reads the data differently, despite no new data being in the CSV.
This is the CSV file I am using:
https://docs.google.com/spreadsheets/d/14OirozvJ33x2ygCvPaJ6KnsGjF7JL3CWJic75fChbxs/edit?usp=sharing
On almost every day the algorithm reads and logs the data correctly, however, on a random day the value will change. On June 5th and 9th, GM's ROA is read as 9, but on the 6th, it is 8.9255685734. The same issue appears with AAPL, but on the 9th.
I've also tried a number of tests to see if I can illuminate the issue any better:
- The issue appears with different securities, at different points in time, with different values for the same securities
- The issue appears with all imported data for that security on that day, ie. two imported data points will both be incorrect
- I've tried creating brand new CSV files, pasting the code into a new algorithm, and running everything in incognito mode
Log Excerpts:
2014-06-05 08:45 before_trading_start:45 INFO Equity(5387 [NOC]) : 55.0 - 4.0
2014-06-05 08:45 before_trading_start:45 INFO Equity(40430 [GM]) : 9.0 - 2.0
2014-06-05 08:45 before_trading_start:45 INFO Equity(5938 [PG]) : 8.0 - 3.0
2014-06-05 08:45 before_trading_start:45 INFO Equity(24 [AAPL]) : 10.0 - 1.0
2014-06-06 08:45 before_trading_start:45 INFO Equity(5387 [NOC]) : 55.0 - 4.0
2014-06-06 08:45 before_trading_start:45 INFO Equity(40430 [GM]) : 8.9255685734 - 1.98345968298
2014-06-06 08:45 before_trading_start:45 INFO Equity(5938 [PG]) : 8.0 - 3.0
2014-06-06 08:45 before_trading_start:45 INFO Equity(24 [AAPL]) : 10.0 - 1.0
2014-06-09 08:45 before_trading_start:45 INFO Equity(5387 [NOC]) : 55.0 - 4.0
2014-06-09 08:45 before_trading_start:45 INFO Equity(40430 [GM]) : 9.0 - 2.0
2014-06-09 08:45 before_trading_start:45 INFO Equity(5938 [PG]) : 8.0 - 3.0
2014-06-09 08:45 before_trading_start:45 INFO Equity(24 [AAPL]) : 1.4286 - 0.14286
2014-06-10 08:45 before_trading_start:45 INFO Equity(5387 [NOC]) : 55.0 - 4.0
2014-06-10 08:45 before_trading_start:45 INFO Equity(40430 [GM]) : 9.0 - 2.0
2014-06-10 08:45 before_trading_start:45 INFO Equity(5938 [PG]) : 8.0 - 3.0
2014-06-10 08:45 before_trading_start:45 INFO Equity(24 [AAPL]) : 10.0 - 1.0
Further update:
- So on June 9th 2014, AAPL underwent a 7:1 stock split, which it appears is being applied to the ROA data for that date. (i.e. while it should read as 10, 10/7=1.4286)
- Meanwhile, June 6th 2014 was GM's Ex-Div date
So I'm not sure entirely what the issue is it appears to be related to stock splits and/or dividend dates. I've run the back test over longer periods of time and the data consistently has this issue on Ex-Div dates and when stock splits occur.
As always, any input would be greatly appreciated, thanks!!