0

I am trying to extract data using API from the web. What I am trying to do is call for the current/historical stock prices of certain company and store the information in a dataframe or a sqlite database. I have a code where I can call for information for one company. But how do I create a loop where I can substitute the ticker symbol (a list of codes representing each company’s name – for which I have a separate dataframe) of each company and store its prices. The intention is to store prices of ALL companies (~500) in one dataframe under their ticker symbol. Here is the code that I have to pull the price of one stock.

data=requests.get('https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=NSE:HDFCBANK&outputsize=full&apikey={}'.format(api_key))

I need to replace NSE:HDFCBANK with a list of other tickers in another dataframe which has list like this 

df1 = {'Ticker': ['HDFCBANK', 'SBIN', 'ADANIGAS', 'BAJAJAUTO'],
'Name': ['Hdfc Bank', 'State Bank of India', 'Adani Gas', 'Bajaj Auto']}

df1 = pd.DataFrame(df1, columns = ['Ticker', 'Name']) 
df1

Very new to Python. Will much appreciate your help. Thanks.

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
user13458293
  • 1
  • 1

1 Answers1

1

Similar to a list, you can iterate over elements in a column of a dataframe. You can replace the ticker symbol using .format() just like you already have done with your api_key (Values get placed in the respective {} present in the string).

prices = []
for ticker in df1.Ticker:
    data=requests.get('https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=NSE:{}&outputsize=full&apikey={}'.format(ticker, api_key))
    prices.append(data.json()['Time Series (Daily)']['2020-04-30']['1. open'])
df = pd.DataFrame({'symbol':df1.Ticker, 'price':prices})
SomebodyOnceToldMe
  • 56
  • 4
  • Thanks @SomebodyOnceToldMe for your revert. The code is working but sorry if this sounds naive but it is extracting only one company's stock prices. My objective is to extract prices of all the companies in the ticker list and store it in a dataframe. how does one go about doing that? – user13458293 May 04 '20 at 08:25
  • @user13458293 Code updated! Also I don't think NSE:BAJAJAUTO exists in their database. – SomebodyOnceToldMe May 04 '20 at 09:05
  • Hey @SomebodyOnceToldMe thanks for your answer. '2020-04-30' is throwing an error. I appended yours with mine (to get more info and add current date but it is now throwing error as "ValueError: time data 'Meta Data' does not match format '%Y-%m-%d'" – user13458293 May 04 '20 at 11:38
  • prices = [] for ticker in df1.SYMBOL: data=requests.get('https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=NSE:{}&outputsize=full&apikey={}'.format(ticker, api_key)) data=data.json() for d,p in data.items(): date=datetime.datetime.strptime(d,'%Y-%m-%d') prices.append(data.json()['Time Series (Daily)'][date,float(p['1. open']),float(p['2. high']),float(p['3. low']),float(p['4. close']),int(p['5. volume'])]) df = pd.DataFrame({'symbol':df1.SYMBOL, 'price':prices}) – user13458293 May 04 '20 at 11:40