Back to Community
Reading In a CSV

So I am trying to read in a CSV file with the S&P 500 closing price each day, like so:

fetch_csv("https://www.quandl.com/api/v1/datasets/YAHOO/INDEX_GSPC.csv",symbol='SP5')

The data set has no symbol so i added a custom one (SP5). I am not sure how to access the data imported. I want to know before the trade day starts what the closing price was of the SP 500 was the previous day

14 responses

Hi Daryl

Hard to know what you did however you can access the data like this:

def handle_data(context, data):
hist = _history(data)

# Return if there is not enough data yet  
if hist is None:  
    return  
spy= hist['Close']['SP5'].values  
#or use Price instead of close  
spy= hist['price']['SP5'].values  

hist = _history(data)

The above line doesn't work for me, it looks like it requires more parameters. I tried this but I just get a syntax error:

hist = history(1, 1d,'close_price')  

ok I got this line to work:

hist = history(1, '1d','close_price') 

But now I just get an error trying to read the value:

Runtime exception: KeyError: u'no item named SP5'
or
Runtime exception: KeyError: u'no item named Close'

I tried this both ways:

print str(hist['Close']['SP5'].values)
print str(hist['SP5']['Close'].values)

You need to defne the function

import datetime  
import pytz  
import pandas as pd  
import numpy as np  
import zipline  
import math  
import talib

@batch_transform(window_length=200, refresh_period=0)  
def _history(data):  
    return data  

Also it is very likely that you need to use "price" instead of close
print str(hist['price']['SP5'].values)

Ok to be clear I have:

def initialize(context):
fetch_csv("https://www.quandl.com/api/v1/datasets/YAHOO/INDEX_GSPC.csv",symbol='SP5')

def handle_data(context, data):
hist = history(1, '1d','close_price')
print " IDX: " + str(hist['price']['SP5'].value)

I get this error:

Runtime exception: KeyError: u'no item named price'

note the CSV is publicly available at the link if you want to look at the data

I just wrote the code for you to get it. Just an fyi you need to remember that the functions start to warm up to get the 200 data so you wont get it until the algo has gotten the values.

EG

Clone Algorithm
30
Loading...
Backtest from to with initial capital
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: 54d6ae68cc61a806cd04db9d
There was a runtime error.

I added a line of code to print the values, but I don't see anything showing up in the log

Clone Algorithm
12
Loading...
Backtest from to with initial capital
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: 54d6b1375f9ee40d473e9cdf
There was a runtime error.

You have couple problems, remember that spy5 is an array so it will have the 200 values so if you want to do an average you can use the mean function if you want to get the last value you can do it with the following ines

spy5 = hist['Close']['SPY5'].values  
log.info('This is the average for the full array %s' % spy5.mean())  
log.info('This is the last field in the array %s' % spy5[-1])  

Other good advise is for you to do dome debugging so you can see the contents for each ticker each time that the handle_data gets call. You can do it by
1) Double clicking the numbers in the IDE and you will see a blue pointer on the line
2) Click Build and after some days the algo will warm up and get the data and you will get into the debuger
3) you can just type the name of the variables in the white space "debugger" and type spy5 and you can see the values
4) You can advance and progress line per line so in that way you can see what it is happening

The example code you gave me never makes it past this line:

if hist is None:  
    return

So I don't see how it works. All I want to do is load that CSV and read yesterday's closing cost of the SP 500. I can't use SPY because my weight algorthm uses yesterday's spy closing and compares it to yesterday's SP 500 closing.

I really don't understand why I can't just do some kind of a SQL query against the CSV to pull the value on a specific date. Can you please post a same code that just prints the value from the CSV into the log. I don't think I am using the CSV reader right, and I have no idea how to do it.

Ok I got this to almost work, I have it writing today's value to the log, now I just have to figure out how to go backwards one day.

Clone Algorithm
12
Loading...
Backtest from to with initial capital
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: 54da36e23fbcd60d4f6c04c0
There was a runtime error.

Is there some command that I can just add a day to all the dates in the csv file? that would fix this.

Hi Daryl,

Here is an algo with a workaround addressing your problem. I used slicing to shift the alignment of close prices forward by a day before the CSV's date column is interpreted by fetcher. This way, the data object will contain the previous day's close price when it is passed to handle_data.

Clone Algorithm
19
Loading...
Backtest from to with initial capital
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: 54dbb1b70401943a6ede4dbb
There was a runtime error.
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.

Awesome, thanks everyone!