Pair Trading - Exploring The Low Risk Statistical Arbitrage Trading Concepts

[VJAY]

Could you upload the file?
If I get it right, you have eod files in a directory and you need to append them to master file at the end of the day?

My EOD data file attached

 

[travi]

My EOD data file attached

bhai, its the same file

 

[UberMachine]

bhai, its the same file

@VJAY @travi
Looks both your files are different though the format is same.
Do you have all the files in a common folder?

 

[VJAY]

@VJAY @travi
Looks both your files are different though the format is same.
Do you have all the files in a common folder?

Now not ...But I can place it where you want....master file is in my C drive as mentioned by ncube....eod file in diferent place

 

[UberMachine]

@VJAY @travi

Try this code

import os
import pandas as pd
# Everything in CAPS can be changed

PATH = os.curdir
COLUMNS = ['symbol', 'date', 'open', 'high', 'low', 'close', 'volume']
OUTPUT_FILENAME = 'one_big_file.csv'

collect = []
for folder,path, files in os.walk(PATH):
    for f in files:
        df = pd.read_table('/home/machine/Desktop/New/EQ_01AUG2018.txt',
             header=None, delimiter=',', usecols=range(7),
             parse_dates=[1])
        collect.append(df)
        
final = pd.concat(collect)
final.columns = COLUMNS
final.drop_duplicates().to_csv(OUTPUT_FILENAME)
print(OUTPUT_FILENAME, ' created')

Copy this code and rename it to master.py (you can give it anyname).
This file should be in the same directory where your files are.
So if your eod files are in C:/DATA/EOD then this script must be in C:/DATA/EOD

Open cmd->navigate to C:/DATA/EOD and then run python master.py
This must do the trick

 

[UberMachine]

Now not ...But I can place it where you want....master file is in my C drive as mentioned by ncube....eod file in diferent place

As a cross check, kindly count the number of instruments in the file and the total entries in the csv file so that I can be sure.
You can do this for a year to be pretty much sure

 

[UberMachine]

Basically the NSE EOD Bhavcopy is a comma delimited .txt file, so pandas.read_csv() should be able to assign it to a df.

so now, you have df from your notebook and df1 with EoD Data.

Pseudocode:
1. read comma delimited txt file into df1

# assume, the master CSV has only 100 scrips, originally you have 500 which is now master.csv.
2. A generic script would take each scrip from header in df and search in df1, then return corresponding close from that row
3. returned close is appended to df
4. df dumps its data back to master.csv

sample EoD file attached. They use simple format
scrip, date, O, H, L,C, V

Thanks

Done.
If this works on the first attempt, I could call myself a decent programmer
Once this is done, we could make it into a reusable pattern

 

[ncube]

Guys, Just wrote a python code to update the master file with the daily nse bhavcopy file. Please add the following codes to the PairTrading.ipynb

1. Add the following function into the cell with other functions:
def update_eod(df,eodfile):
eod = pd.read_csv(eodfile, header=None,index_col=[0],usecols=[0,5])
df = df.append(eod.T).dropna(axis=1).reset_index(drop=True)
df.to_csv('C://stockdata.csv')
return df
2. Add a new cell after after the cell which reads the master file and add the following python statement
df = update_eod(df,'C://eod.txt')

How it works:
1. Every day download the EOD NSE bhavcopy text file and rename it as eod.txt, place it in the same folder as master file.
2. Next time you run the PairTrading.ipynb it will append the master file with the eod.txt content and overwrite the master file with updated data.

Hope this helps..Enjoy Pair Trading..

 

[ncube]

@VJAY @travi

Try this code

import os
import pandas as pd
# Everything in CAPS can be changed

PATH = os.curdir
COLUMNS = ['symbol', 'date', 'open', 'high', 'low', 'close', 'volume']
OUTPUT_FILENAME = 'one_big_file.csv'

collect = []
for folder,path, files in os.walk(PATH):
    for f in files:
        df = pd.read_table('/home/machine/Desktop/New/EQ_01AUG2018.txt',
             header=None, delimiter=',', usecols=range(7),
             parse_dates=[1])
        collect.append(df)
     
final = pd.concat(collect)
final.columns = COLUMNS
final.drop_duplicates().to_csv(OUTPUT_FILENAME)
print(OUTPUT_FILENAME, ' created')

Copy this code and rename it to master.py (you can give it anyname).
This file should be in the same directory where your files are.
So if your eod files are in C:/DATA/EOD then this script must be in C:/DATA/EOD

Open cmd->navigate to C:/DATA/EOD and then run python master.py
This must do the trick

Oh did not see this message earlier, good code, one can also use it to run it from outside jupyter....keep it up.

 

[UberMachine]

Guys, Just wrote a python code to update the master file with the daily nse bhavcopy file. Please add the following codes to the PairTrading.ipynb

1. Add the following function into the cell with other functions:
def update_eod(df,eodfile):
eod = pd.read_csv(eodfile, header=None,index_col=[0],usecols=[0,5])
df = df.append(eod.T).dropna(axis=1).reset_index(drop=True)
df.to_csv('C://stockdata.csv')
return df
2. Add a new cell after after the cell which reads the master file and add the following python statement
df = update_eod(df,'C://eod.txt')

View attachment 27044

How it works:
1. Every day download the EOD NSE bhavcopy text file and rename it as eod.txt, place it in the same folder as master file.
2. Next time you run the PairTrading.ipynb it will append the master file with the eod.txt content and overwrite the master file with updated data.

Hope this helps..Enjoy Pair Trading..

Hi,
I think you could post a link to the jupyter notebook or could upload it to github so that it could be easy for all.

I believe for the above code to work , I should run it only once a day so that the entries aren't duplicated.