Python - how to include each stock ticker in a new column

Question

I just started python two weeks ago and have looked into a code to get stock data from Tiingo. I managed to get all the data i need from running the below code. However, i cannot seem to include the actual stock 'ticker' in the output in excel. Anyone have any clue as to why? All help is appreciated!

Thanks!

import datetime as dt
import pandas as pd
from pandas import DataFrame
import numpy as np
import os # creates new directories
import matplotlib.pyplot as plt
from matplotlib import style
import pandas_datareader.data as web
import pandas_datareader as pdr
from scipy import stats
import bs4 as bs
import pickle
import requests
import lxml
import win32com.client as win32
import xlsxwriter

tiingo_api_key = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx' #'YOUR API KEY'

tickers=[ 'EWA', 'EWO', 'EWK', 'EWC']

i = 0
h = 0
year = 252  # trading days
month = year/12
years = 5

dtToDate = str(dt.datetime.now().date())
print(type(dtToDate))
dtFromDate = '2014-01-01'
print("Start Date: " + dtFromDate)
print("End Date: " + dtToDate)

def get_data_from_tiingoMarketsDaily():

    dfPriceDataday = pd.DataFrame()

for ticker in tickers:  
    print('Processing Stock Data for: ' + ticker)
    request_url = "https://api.tiingo.com/tiingo/daily/"+ticker+"/prices?startDate="+str(dtFromDate)+"&endDate="+str(dtToDate)+"&token="+tiingo_api_key
    data = pd.read_json(request_url)[['date', 'adjOpen', 'adjHigh', 'adjLow', 'adjClose', 'volume']]

    data['date'] = pd.to_datetime(data['date']).dt.strftime("%d/%m/%Y") 

    data.rename(columns={ 'date': 'Date', 'adjOpen': 'AdjOpen', 'adjHigh': 'AdjHigh', 'adjLow': 'AdjLow', 'adjClose': 'AdjClose', 'volume': 'Volume'}, inplace=True)
    data.sort_index(ascending=False, inplace=True)
    dfPriceDataday = dfPriceDataday.append(data)

writer = pd.ExcelWriter("StockData.xlsx", engine='xlsxwriter')
dfPriceDataday.to_excel(writer,sheet_name='Daily',index=False)
writer.save() 

get_data_from_tiingoMarketsDaily()

Answer 1

I am not sure how Tiingo is returning data but you can fix this by adding Ticker column to Excel file. Make change like this

for ticker in tickers:  
    print('Processing Stock Data for: ' + ticker)
    request_url = "https://api.tiingo.com/tiingo/daily/"+ticker+"/prices?startDate="+str(dtFromDate)+"&endDate="+str(dtToDate)+"&token="+tiingo_api_key
    data = pd.read_json(request_url)[['date', 'adjOpen', 'adjHigh', 'adjLow', 'adjClose', 'volume']]

    data['date'] = pd.to_datetime(data['date']).dt.strftime("%d/%m/%Y") 

    data.rename(columns={ 'date': 'Date', 'adjOpen': 'AdjOpen', 'adjHigh': 'AdjHigh', 'adjLow': 'AdjLow', 'adjClose': 'AdjClose', 'volume': 'Volume'}, inplace=True)
    data['Ticker'] = str(ticker)
    data.sort_index(ascending=False, inplace=True)
    dfPriceDataday = dfPriceDataday.append(data)

Hope this is what you are looking for.