I would like to use specifically Quandl's Google Finance database for downloading stock prices for backtesting a strategy. The reason is that google finance has clean data compare to Quandl's WIKI and Yahoo databases for stocks adjusted for splits and etc. As seen here the last link will show the stock splits adjusted for:
https://www.quandl.com/WIKI/AAPL-Apple-Inc-AAPL
https://www.quandl.com/YAHOO/AAPL-AAPL-Apple-Inc
https://www.quandl.com/GOOG/NASDAQ_AAPL-Apple-Inc-AAPL
However Quandl's google database tags is in the form of GOOG/NYSE_IBM or GOOG/NASDAQ_AAPL for example, which differ from the tags as such WIKI/IBM, YAHOO/IBM.
Since it is not feasible to add the NYSE or NASDAQ tags by hand for the amount of stocks listed on those exchanges, is there an efficient way to download stock data from Quandl given a list of stocks in a csv or pandas dataframe?
Here is my code FWIW:
nyseList = pd.read_csv('dowjonesIA.csv') # read csv
masterList = pd.DataFrame(nyseList.Ticker) # save symbols only into another df
for index, rows in masterList.iterrows():
ticker = masterList.loc[index] # this will not work for passing element
stock = Quandl.get(ticker, trim_start="2000-01-01", trim_end="2015-01-01")
#stock = Quandl.get("GOOG/NASDAQ_AAPL", trim_start="2000-01-01", trim_end="2015-01-01") #this is the actual format that works
# lags data for signal
stock['diff'] = (stock.Open - stock.Close.shift(1))/stock.Close.shift(1)
lowerBound = -0.08
upperBound = 0.08
#generate signal based on 8% rule
stock['signal'] = np.where(stock['diff'] >= upperBound, 1.0, np.where (stock['diff'] <= lowerBound, -1.0, 0.0))
initialCapital = 100000.0
accountLimit = 0.05
#calculate size based on account risk and price
stock['position'] = (stock.signal*initialCapital*accountLimit)/stock.Open
#shows if there is a position open
stock['open trade'] = np.where(stock['position'] > 0, 1.0, np.where(stock['position'] < 0, -1.0, 0.0))
#determine profit/loss
stock['pnl'] = (stock.position*stock.Close) - (stock.position*stock.Open)
#sums up results to starting acct capital
stock['equity curve'] = initialCapital + stock.pnl.cumsum()
print(stock.head(20)) # is dataframe
# plots test results
stock['equity curve'].plot()
plt.show()
I have tried using pandas built in remote data access and that too gives problems when passing strings as stock symbols for args. Also any suggestions to execute the loop in a vectorized way is appreciated instead of iteratively, and for general logic flow. Thanks in advance.
Never mind I just appended the tag as string to the stock symbol string. This format will work:
masterList = pd.Dataframe('GOOG/NYSE_' + nyseList['Ticker'].astype(str))
Credit to this thread: Append string to the start of each value in a said column of a pandas dataframe (elegantly)