1

Im trying to build an excel sheet that calculates synthetic options prices and greeks for time series data to model intraday options pricing, input is simply intraday price data, say Tick level to 5 minute interval. I found this https://www.thebiccountant.com/2021/12/28/black-scholes-option-pricing-with-power-query-in-power-bi/ which provides for powerBI and Black Scholes but possibly not very accurately. I prefer the Binomial method (I have used this excellent tutuorial to build a manual version for a large number of strikes but it takes a long time to calculate and is very very complex and also inaccurate due to not being able to calculate many steps before topping excel out: https://www.macroption.com/binomial-option-pricing-excel/).

Does anyone have any idea if this is possible to create an entire column in Power Query that will calculate bionomially derived options pricing using >100 even up to 1000 steps? The reason is intraday pricing using high resolution data 5min, 1min, Seconds and Tick I think needs a large number of steps to properly converge. This is just about doing a good enough model that can be used for visualising the progress of a trade on a given day.

Any pointers on how this could be done and calculated using M Language would be much appreciated and useful!

dan1st
  • 12,568
  • 8
  • 34
  • 67
TimExcellent
  • 11
  • 1
  • This sounds interesting but is likely too broad to be considered "on-topic" for StackOverflow. – Alexis Olson Dec 31 '21 at 20:38
  • I would use `List.Generate` with recursion or `List.Generate` + `List.Accumulate` to create the **Price Tree**. I didn't examine the rest of the modelling, but it doesn't look to difficult. – Ron Rosenfeld Dec 31 '21 at 22:13
  • Thanks @RonRosenfeld , yeah hope its not to broad a subject Alexis says above I'm running into problems like finding the equivalent M language function for things like NORM.DIST seems to be a crux point for me. – TimExcellent Dec 31 '21 at 22:41
  • Huh? I didn't see where `NORM.DIST` was mentioned in the reference you posted regarding the binomial pricing methods. I suggest you create the basic price and option trees described, and then modify them depending on which model you decide to use. Post back with your code if you run into trouble. – Ron Rosenfeld Jan 01 '22 at 02:48

0 Answers0