How to update instead of rewriting csv by python with pandas to fetch stock data?

Question

I am an absolute noob in terms of programming.
I wish to fetch historical data of a list of stock from yahoo for data analysis.
I modified the script I found and got this.

#settings for importing built-in datetime and date libraries
#and external pandas_datareader libraries

import pandas_datareader.data as web
import datetime
from datetime import timedelta


#read ticker symbols from a file to python symbol list
symbol = []
with open('E:\Google drive\Investment\Python Stock pick\Stocklist1.txt') as f:  
    for line in f:
        symbol.append(line.strip())
f.close

end = datetime.datetime.today()

start = end - timedelta(days=400)

#set path for csv file
path_out = 'E:/Google drive/Investment/Python Stock pick/CSV/'


i=0
while i<len(symbol):
    try:
        df = web.DataReader(symbol[i], 'yahoo', start, end)
        df.insert(0,'Symbol',symbol[i])
        df = df.drop(['Adj Close'], axis=1)
        if i == 0:
            df.to_csv(path_out+symbol[i]+'.csv')
            print (i, symbol[i],'has data stored to csv file')
        else:
            df.to_csv(path_out+symbol[i]+'.csv',header=True)
            print (i, symbol[i],'has data stored to csv file')
    except:
        print("No information for ticker # and symbol:")
        print (i,symbol[i])
        i=i+1
        continue
    i=i+1

And I run the script everyday and it fetches stock data in the past.
It would replace the entire csv file and always replacing the old data with the new one.

Is there anyway for the script to just add the new data into the csv file?

Thanks a lot in advance. I am all new to the programming world and have no idea how to do this.

score 1 · Accepted Answer · answered Jun 29 '19 at 10:29

1

I think you need to add 'a+' instead. Otherwise the file will keep looping itself. It is what happened to me.

answered Jun 29 '19 at 10:29

Mandy Zou

130
1
8

import pandas as pd import datetime from pandas_datareader import data import time as t def techanalysis(): start = datetime.datetime(2021,3,10) end = datetime.date.today() #time(2021,3,10) list=['AAPL','FB', 'MSFT','VTSAX'] DF = data.DataReader(list, "yahoo", start,end) techanalysis() with pd.ExcelWriter('analysis_sheet', mode='A') as writer: DF.to_excel(writer) t.sleep(60*2) – Hari Aravi Mar 28 '21 at 15:40

score 0 · Answer 2 · answered Jan 14 '19 at 08:01

0

You have to add param 'a':

with open('E:\Google drive\Investment\PythonStockpic\Stocklist1.txt','a') as f:
    f.write(line.strip())

see: append new row to old csv file python

answered Jan 14 '19 at 08:01

Oleksandr Trunov

39
6

I will try it later. But by doing so, will the new 400 rows of data adding onto the end of csv, making most of them duplicate? – Max Cheung Jan 14 '19 at 08:08
It will just append the new data to csv file. It will not check if csv has the same data (row). – Oleksandr Trunov Jan 14 '19 at 08:12
Is it possible to script it so that it will check if the csv has the same data (row)? – Max Cheung Jan 14 '19 at 08:14
1

@MaxCheung yes, I think you have to check row by row, if a some row contain data already.For example: `for line in csv_file: if data in line: found = True break` see this post: [How to check if an data already present in a CSV file using Python](https://stackoverflow.com/questions/20212261/how-to-check-if-an-data-already-present-in-a-csv-file-using-python) – Oleksandr Trunov Jan 14 '19 at 08:24
to be honest, I have no idea what is going on inside the script. But I will try... – Max Cheung Jan 14 '19 at 08:31

How to update instead of rewriting csv by python with pandas to fetch stock data?

2 Answers2