Thread by @paststat

let me do an option backtest seminar so that folks can do their own tests , than bugging me to do that and to do this et.al , all these am doing in real time , so don't interrupt in between

step down load index data -> nseindia.com/products/conte…

step 1 ) link - > dropbox.com/s/tves7ywbhwax…

step 2 ) we add few parameters like t % chg today , and weekly expiry days via vlookups ( there are better ways if some one is good at marco's and vb et.al ) but for davinci sake we stick to vlookups and such simple functions

t%
is current days change in percecnt ,
DoW is day of the week
5H/L , am simple looking whether it is 5 day hi or 5 day lo , if not am marking "-" ,
Gap is simply todays open minus yesday cls
Cdl is candle in pts , that is today close minus todays open

5DMA is current days close in percent reference to last 5 days simple moving average
DTE - days to expiry , that is current date minus the nearest weekly expiry ( acheived via vlookup )
t+1%t+1t+2%t+2t+3%t+3t+4%t+4t+5%t+5

are basically look ahead one day change in percent and in point terms ,
our sheet now becomes like this

do look at the left top , it is a licenced copy to kora reddy , unlike jeep driver and others who uses cracked excels and certain 200 inr cracked amibrokers seller from pune who now graduated in selling some farzi charting software for 20k upwards

we basically get this new excel sheet -> dropbox.com/s/9n8j3eqq5w9n…

we will break for a cigarette mean while asked sonny to download options data from nseindia.com/products/conte…

for simplicity sake i just asked sonny ( who is my assistant and whom i'm teaching in class kind of tutorial ) just download expiry day's files from star of feb when weeklies started on nifty

back after 5 minutes

after the nicotine shot , we can write simple python script or manual download data , as using child labor for manual downloading of each days file is punishable offence

if sonny complaints to wifi or the grand parent(s) that i made him such heavy duty job , need less to say they will put be in dog house for the next few days

anyway , here the nifty options raw data extracted from daily bhavcopies of Archives of Daily / Monthly Reports (FO)
11 feb to till date ,

we also do double double sort ( you see it is all part of data hygiene habits ) on trade date and then by expiry date , then by options strike , then by CE/PE ,
it makes things easier if you jsut want observe soemthign later

here the file link ->
dropbox.com/s/ff8lbs0t0m4j…

looks like the file is from 11 feb 2019 till 12 apr 2019

now what we need is an identification function to nifty+strike+CE+expiry , there are n number of ways to achieve it , but for simplicty sake we resort to concatenate function

and we have headings like DateClosePEexitexpiryEntryExitP/LCEEntryExitP/L

Date is the date of our jpm option trading system algo signal generation date
close is nifty idx close on that date
PE is put strike we are observing ,
i simply use =ROUND(B2*0.98,-2) for a 2% away put strike , as an example

exit : is wchich date the JPM trading desk is hypothetically exiting the options ..
we also model our trade signals from close to close btw
expiry : which expiry we are trading

P/L is generally because of the long term drift thingy ( note sensex has given 50000 % returns since 80 till date with dividends) , and we assume we are short puts here , so it entry price minus exit price , and for CE , it is exit price minus entry price

something like this we get a screenshot , with our forumlas

as i said vlookup is very powerful than any fighter jet out there , my guess is it would have made atleast 87954666 traders go broke so far , which is more than both civilains and army personaal killed by bombs since wwii

anyway , we are simply using concatenate function and using vlookup to search for the date+strike+ce/pe+expiry ( thats the gist ) , you can verify that in the beloe excel file # 4)

here is the excel file # 4) -> dropbox.com/s/49ecbgd2ilpx…

time for some algos
i will just restrict to one - >
algo rules ) short ATM PE on upday
, looks like the nifty idx basic formulas we didn't create a column called expiry date for the current date , so we add that and here is updated sheet of the same -> dropbox.com/s/d9bimq11a5p8…

ooops , again forgot to add exit colum, we just if else condition some thing like in O7 onwards we type -> =IF($O$1="t+1",A5,IF($O$1="t+2",A4,IF($O$1="t+3",A3,IF($O$1="t+4",A2,IF($O$1="t+5",A1)))))
and in O1 we key in our exit criteria as t+1 to t+5 , meaning our hold period is

1 to 5 days et.al

here is the updated nifty idx basic forumals updated version 2 sheet -> dropbox.com/s/1yqjmp4l6z59…

back to our algo , short atm pe on upday ,
so we go to nifty idx basic formula updated version 2 sheet and filter column h for grater than zero values and simply take those nifty closes and t+1 and expiry date details and paste then in the nifty options look up sheet

there was slight error , ( not again ) in the nifty idx basic formula updated version 2 , basically the col p lookup is searching for current dates expiry , while it should be checking for the exit dates expiry
here is the updated ver 3 sheet ->
dropbox.com/s/6iacyyvydf7t…

back to our algo , of shorting atm pe on an up day and holding for 1 day ->
simply paste the dates and nifty idx close values and t+1 and epiry dates
we get to see whats happening to our short put and long calls

thats it , lets sell this algo JPM and GS !!

this is the final sheet for quick backtest -> dropbox.com/s/g526wy2bllr3…

and our algo of shorting atm -pe when nifty is up and hold the short put for a day , backteest results ->