Back to Community
Fetch CSV Data Issues Associated with Stock Splits and Ex-Div Dates
Steven Denison
edited
seeking help fetcher using data Q updates

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!!

Clone Algorithm
6
Loading...
Backtest from to with initial capital
Cumulative performance:
Algorithm Benchmark
Total Returns
--
Alpha
--
Beta
--
Sharpe
--
Sortino
--
Max Drawdown
--
Benchmark Returns
--
Volatility
--
Returns 1 Month 3 Month 6 Month 12 Month
Alpha 1 Month 3 Month 6 Month 12 Month
Beta 1 Month 3 Month 6 Month 12 Month
Sharpe 1 Month 3 Month 6 Month 12 Month
Sortino 1 Month 3 Month 6 Month 12 Month
Volatility 1 Month 3 Month 6 Month 12 Month
Max Drawdown 1 Month 3 Month 6 Month 12 Month
# Backtest ID: 5a1ddc437f303d4069d41920
There was a runtime error.
3 responses
Steven Denison

Not to beat a dead horse, but does anyone have any idea about this whatsoever? Or has anyone maybe seen similar issues when using fetch_csv? This feels like some sort of back end bug but I'm really not sure.

Jamie McCorriston

Steven, that looks like a bug. I have not seen that one before and you're right that it seems to be tied to adjustments (splits/dividends). Unfortunately I don't have any ideas for a workaround right now, but I'll see if I can come up with something. Let me know if you come up with anything yourself.

The good news is that we are actively working on improvements to how you can use your own data on Quantopian.

Disclaimer

The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by Quantopian. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. No information contained herein should be regarded as a suggestion to engage in or refrain from any investment-related course of action as none of Quantopian nor any of its affiliates is undertaking to provide investment advice, act as an adviser to any plan or entity subject to the Employee Retirement Income Security Act of 1974, as amended, individual retirement account or individual retirement annuity, or give advice in a fiduciary capacity with respect to the materials presented herein. If you are an individual retirement or other investor, contact your financial advisor or other fiduciary unrelated to Quantopian about whether any given investment idea, strategy, product or service described herein may be appropriate for your circumstances. All investments involve risk, including loss of principal. Quantopian makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances.

Steven Denison

Just to provide an update for anyone with similar issues, I've managed to work around the issue by forcing the algorithm to confirm the new target portfolio remains the same for two consecutive days before rebalancing.