Managed to get P&L for options using only google sheets formulas

[wxytrader]

So I have been tinkering with my spreadsheet. I have always had a P&L chart for @ expiry, but wanted to have it track x days into the future. I first tried to do this using API/Imports etc but that can be unreliable if you are scraping, or costs money and at the time the data I was getting was delayed. It also isn't very efficient because you have to import the specific data manually for each position you open...unless you can write some script to import data for the specific symbol, strike, expiry etc which is a whole other endeavor.

I decided to just do the calculations in google sheets using formulas that can be found online. There is no importing of data here, and no script running in background. The only data I enter is the IV and the spreadsheet calculates the rest.

As you can see the results are pretty accurate.

Long call

Long Put

Bull put spread

 

[Quanto]

Can your code calc also these essential expiry metrics for each strategy? :
- MaxProfit
- MinProfit
- S0Profit (ie. the PnL when stock at expiry closes at the same price like at entry)
- BEP (Break/Even point)

Some strategies, for example Butterfly, have 2 Break/Even points, and also 2 MinProfits, and some others even have 2 MaxProfits...

I lately had a hard time to calc these for LongPutButterfly when the long strike distances are different.
My use-case is a little bit different: I'm not doing it in GUI but doing it inside an options scanner (ie. such a scanner does not have any GUI).

 

[wxytrader]

Can your code calc also these essential expiry metrics for each strategy? :
- MaxProfit
- MinProfit
- S0Profit (ie. the PnL when stock at expiry closes at the same price like at entry)
- BEP (Break/Even point)

Yes all of those...but only for same expiry. Also the theoretical P/L only works with singles, and spreads...not with 4 legs like Iron condors yet...those are just P/L @ expiry.

The above screenshots are showing the P/L on April17 for options that have an expiry of May17...so showing profits 30 dte or 55 days from today if stock price was 200.

 

[Quanto]

Yes, same expiry is ok (called "verticals"). Differing expiries (called "calendars" and "diagonals") give real curves as PnL diagram, no lines, and make compuattions very hard (for them one needs to apply BSM, whereas the verticals can really be computed by the user very easily w/o any higher maths).

 

[wxytrader]

Yes, same expiry is ok (called "verticals"). Differing expiries (called "calendars" and "diagonals") give real curves as PnL diagram, no lines, and make compuattions very hard (for them one needs to apply BSM, whereas the verticals can really be computed by the user very easily w/o any higher maths).

Yes only verticals. Could probably add calendars but I don't trade calendar spreads. This is mostly so I can integrate my positions automatically into the sheet and it will track it from then on. It's cool because the P/L chart now updates as the stock price changes.

 

[BeerLeagueStonkTrder]

Sorry if this is a dumb question - totally new here to ET:

What platform are you using where you're getting edge on vertical/calendar/diagonal options spreads?

 

[Quanto]

Sorry if this is a dumb question - totally new here to ET:

What platform are you using where you're getting edge on vertical/calendar/diagonal options spreads?

I'm analysing options chains (ie. the strike tables) and create some metrics, like Break/Even distance%, PnL% and some other metrics... Ie. looking fior possible trades that fulfil my own criteria...

I don't know of trading plaforms specific for this, except some online sites with options scanners/screeners, like this one:
https://marketchameleon.com/volReports/VolatilityRankings

See under the menu item Screeners for many screeners for spreads and other options strategies like butterfly etc.
But I think these advanced features require a paid subscription (I have no experience with their paid subscriptions).

 

[themickey]

I decided to just do the calculations in google sheets using formulas that can be found online. There is no importing of data here, and no script running in background. The only data I enter is the IV and the spreadsheet calculates the rest.
As you can see the results are pretty accurate.

You've done a nice job with the Sheets charts, well done.

 

[jamesbp]

You can save yourself a lot of pain (and inaccurate RiskGraphs) by using something like Peter Hoadley's Excel Add-In ... quite superb and great value for money

https://www.hoadley.net/options/strategymodel.htm

 

[wxytrader]

You can save yourself a lot of pain (and inaccurate RiskGraphs) by using something like Peter Hoadley's Excel Add-In ... quite superb and great value for money

https://www.hoadley.net/options/strategymodel.htm

Yeah I think I tried that out before. Great work! However it uses Excel which requires a subscription, and at the end of the day its a calculator to add positions in your portfolio to, where I wanted a calculator to add to positions in my portfolio.

No problem with inaccuracies, as you can see in this price forecast for an ATM IWM May17 195 put on Apr17:

Tos price is about 4.62 because open price is at 5.62...this discrepancy is due to tos calculating for dividends causing price of puts to increase.

Note if you flip the put to a call OSET returns 5.34 and mine returns 5.33 so bang on