How to create an Excel table containing butterfly prices

Kevin Lee

Member
Hi all,
I did an interview with John Locke few weeks ago and during the interview I showed an Excel table that I use to monitor butterfly prices. I received emails asking me how that table was generated. So I thought it's easier if I just record a video to show. Here it is....

 
Great tutorial, Kevin! A couple of shortcuts handy in this workflow:

Ctrl+Shift+<arrow> selects all cells from the current one to the end of available data in the direction of the arrow. When you select all data to copy from an exported .scv, after clicking the top left cell (A1), just hold down Ctrl and Shift together, and press the down arrow and then the right arrow.

To paste the copied data at the bottom of a (pretty large) main datasheet, instead of scrolling manually just press Ctrl+End. Ctrl+End goes to the bottom of available data.
 
Kevin,
Many thanks sharing with members of community various nuances of your trades. Watching your January presentation on JL site was very inspirational for me .
It really motivated me to stop looking for next best thing and put most of my time and efforts in learning M3.
The more videos I watch and the more back trading I do, I appreciate more and more the depth of your knowledge about this trade.

I plan to implement your system for monitoring BF prices into my daily routine .
As you have been monitoring BF prices daily for quite some time, I would like to ask you a few questions:

1) By keeping track of 10 point interval strikes, do you find any significant importance where the current market trades and mid prices of butterflies ? For example if there is any material difference in the price of 1060P/1010P/960P butterfly with RUT at 1031 , 1035 and 1039 ? Do you factor this in your analysis of daily records of butterfly prices ?

2) Do you find any pattern when at certain days of the week or days to expiration butterfly prices gain or loose value more than the model predicts?

3) Do you keep daily records of volatility skew together with butterfly prices ?
Sergei.
 
Last edited:
Kevin,
Many thanks sharing with members of community various nuances of your trades. Watching your January presentation on JL site was very inspirational for me .
It really motivated me to stop looking for next best thing and put most of my time and efforts in learning M3.
The more videos I watch and the more back trading I do, I appreciate more and more the depth of your knowledge about this trade.

I plan to implement your system for monitoring BF prices into my daily routine .
As you have been monitoring BF prices daily for quite some time, I would like to ask you a few questions:

1) By keeping track of 10 point interval strikes, do you find any significant importance where the current market trades and mid prices of butterflies ? For example if there is any material difference in the price of 1060P/1010P/960P butterfly with RUT at 1031 , 1035 and 1039 ? Do you factor this in your analysis of daily records of butterfly prices ?

2) Do you find any pattern when at certain days of the week or days to expiration butterfly prices gain or loose value more than the model predicts?

3) Do you keep daily records of volatility skew together with butterfly prices ?
Sergei.

Hi Sergei,

I'm glad what I shared could help. My answers to your questions :

1. The analysis of options behavior isn't an exact science. Unlike physics or engineering where the measurements are precise, options analysis is at best a crude approximation of reality. Therefore, analyzing 10 pts strikes is probably good enough and if RUT is in between the strikes, I think it doesn't really matter.

One thing I have to highlight is that the butterfly prices recorded in the table are merely mid prices, it doesn't mean an actual trade can be filled at those prices. What it does show is approximately where the reasonable price should be. The way the table helps is it tells me when there is big mismatch in prices.

2. I don't see any predictable prices patterns by days of week or by expiration cycle. I wish I could say butterfly prices fall the most on Fridays but I really haven't found evidence for that. But what I'm doing might not be systematic and scientific enough to make that conclusion.

However, what I did observe is that the Put butterfly prices tend to be lower than Call butterfly prices when on down days or down trend. The reverse is true on the upside. Again, this is comparing mid prices. It doesn't mean you can fill Put butterflies at prices lower than call butterflies. The fill-able price is probably in between Put and Call. The interesting thing though is if you try to buy Put butterfly at mid price in a down day, it can be much harder.

3. Yes, I do monitor volatility skew together with butterfly prices. The most important thing is to watch where the legs of the butterflies are on the IV skew. If they are in the wrong places, then that's a cause for adjustment.

Hope this helps.
 
3. Yes, I do monitor volatility skew together with butterfly prices. The most important thing is to watch where the legs of the butterflies are on the IV skew. If they are in the wrong places, then that's a cause for adjustment.

Kevin, I do not want to be a pig and hope it will not be too much to ask you to make a short video where you will show how you collect, process and interpret volatility skew data. A couple of examples where you adjusted because legs were in wrong place would be a great help for anyone who is learning M3 trade. Many thanks in advance.
 
Hi,
Actually Ron Bertino has done an in depth discussion of how skew works and how it affects a trade, using a IV skew comparison graph I did. It very well done. Have you seen it ?

 
Kevin,

I watched the episode of CD TG 2 session where Ron Bertino at great length discussed a volatility skew effect.

During this session he mentioned the article which Dan Neagoy wrote on Dec 3, 2014 in his trading blog
http://www.thetatrend.com/spike-implied-volatility-can-help-short-vega-options-income-trade/

After Dan’s presentation on CD Round table on Dec 16, 2015 Dan Neagoy wrote a follow up article about the same subject:
http://www.thetatrend.com/implied-volatility-riddles-do-i-stand-corrected-on-vega/

I had Bearish Butterfly trade which I initiated on Jan 4, 2016 and closed on Jan 29, 2016.
I decided to to do a detailed study of this trade with the idea to understand better a volatility skew effect in action.
Some of MIV were missing in OV and I left those cells blank.
2016-02-28 BBF 1030-1080-1130 prices Feb16.png
After close examination of this trade I made the following conclusions:
1) The price of options is mainly driven by supply and demand and could deviate significantly from theoretical values.
OV mid price of butterflies fluctuated from 5.90 to 11.40 during 4 days on Jan 15,19,20, 21 with RUT being in 10 points range.
It is very important to understand when butterfly prices are overpriced and underpriced and it is essential for a long-term success to keep continuous track of butterfly prices.

2) Time premium in deep in the money options is significantly lower than in ATM or OTM options.
Although visually on volatility skew graph in percentages , the effect of volatility drop is more pronounced for DIM options than for ATM or OTM , but because Time Premium is much smaller, it’s effect due to volatility drop in dollar value of the option is very minor and the biggest negative impact comes from largest negative delta of DIM position and not from volatility drop.
2-28-16 IWM Put Butterfly Skew.png
As an example I created the chart with all individual greeks for each component of Bearish Butterfly from Dan’s article .
2016-02-28 BBF IWM study.png
Please let me know if my conclusions are correct.

Sergei
 
These are good info to study. After looking at IV changes for a period of time, you will develop an intuitive sense of how they change with time and underlying movement. I find that developing a deep understanding of the anatomy of a butterfly trade is more beneficial than learning multiple styles of trading butterflies. Because once you have that intuitive feel, you'll naturally come up with sensible approach to trading the position.

The second article you linked is a very good one. The findings are consistent with my own research and live trading experience. Bearish butterfly is very resilient to downward movement, not just because it has negative delta (this one is obvious) but also because its vega isn't as negative as what is shown in the analyzer. Despite the fact that butterfly has negative vega theoretically, as the article has shown, depending on the IV skew movement and the magnitude of the IV increase, the weighted vega of butterfly can be neutral or even positive. Therefore, an increased IV can actually help a bearish butterfly vega-wise. I know this is completely counter intuitive to what we were taught as a student, just like the weighted vega of a calendar can be negative, but if we look at enough data and spend enough time monitoring, we'll realize that's the reality.

As the article showed, when market drops, IV goes up, but IVs of strikes below money, ie OTM Puts, increase much less than those above money (I say below money strikes because the same strikes are ITM for Calls. Just to avoid confusion.). Sometimes, the IV skew rotates counter clockwise (ie flatten) so much that some of the lower strike IVs actually decrease. As a result, IVs of the 2 butterfly short strikes are usually quite well protected as long as you keep them tugged below market. So we end up seeing bearish butterfly being more resilient than expected in a downward market. One side note though - when there is crisis-level fear, the entire IV curve gets lifted up vertically. All bets are off then. That's when the vertical movement overwhelms the effect of IV skew change and as a result, even bearish butterfly will hurt in a big way.

In a typical situation, the BB resilience starts to wear out when market hits short strike on the way down. By the time RUT is 10 to 20 pts below market, the IV increase will hurt the short strike more than expected and at the same time, delta turns positive. So, a butterfly will be hurt by delta and vega simultaneously plus if it's near expiration, by gamma as well. That's a triple whammy. P&L will meltdown if RUT continues to go down. Therefore, typically, I'd shift the shorts down or the entire butterfly down when RUT is between 10 to 20 pts below the short strikes, regardless of the position delta or the T+0 line. This has been my practice for a while.
 
I've upgraded to v7.87 today. Besides fixing the critical problems of 7.86, what I'm most excited about is the ability to add suffix to the file name to be exported. Before this version, the exported file will overwrite any file with the same file name. Therefore, the auto download feature was meaningless. Now it's possible to let OV automatically collect options data for us.

To auto download OV data to csv file, you just need to add "yymmdd hhmmss" to the back of whatever file name you choose. Then specify how frequently you want to download and when to start and stop. Then click OK.

The ability to download data into Excel is highly valuable. We can now analyze any kind of trends we like. I used to do this with Excel TOS DDE, but it's quite annoying because DDE keep freezes up depending on the load and recording will stop. I've started the auto download. Seems to be working .....
clear.png


upload_2016-3-1_10-15-55.png
 
Thanks for sharing Kevin. This is big and something I asked OV for recently. Extremely happy to see that they included this.
 
Thanks for sharing Kevin. This is big and something I asked OV for recently. Extremely happy to see that they included this.

OV had this for a long time according to Len but very few know about it. It just sat there dormant. I told Len this is huge and urged him to fix the file name issue. I am glad too that this is now available.
 
I am running into an issue trying to get prices for the current day (today 3/2). When I create an export file it is loading it with yesterday's day and the time looks to be for the final prices of the day ( 3/1 15:14).

I tried creating these files using back trader and just live using the scheduling option. In either case it still has yesterday's date and time.

Anyone else having this problem? Is this a bug or can you only get prices from the prior day(s)?
 
After looking at IV changes for a period of time, you will develop an intuitive sense of how they change with time and underlying movement. I find that developing a deep understanding of the anatomy of a butterfly trade is more beneficial than learning multiple styles of trading butterflies. Because once you have that intuitive feel, you'll naturally come up with sensible approach to trading the position.

Kevin, After watching your interview with John Locke , I am under impression that you manage your whole monthly campaign as one large position regardless of the fact that you open different tranches at different times. Please correct me if I am wrong.
I watched many episodes of CD Trading Group 2 where Tom Hughes showed his live M3 trades in progression. His method is to enter every 2 weeks and maintain each campaign individually even for same monthly expiration cycles . I also remember that other M3 trader Vincent Lim from Singapore wrote on old M3 thread that he uses similar method as Tom Hughes.
After thinking about this, I suspect that you with your deep knowledge of this trade no longer look at your positions as a base M3 group but more look at individual components and have pretty good idea how each guy in your orchestra performs (depending on its position inside the tent or outside from greeks point of view ) if market goes up, down, volatility drops, rises , etc .
Would love to hear your opinion about pros and cons of yours and Tom's method.
Thanks in advance .
Sergei
 
Kevin, After watching your interview with John Locke , I am under impression that you manage your whole monthly campaign as one large position regardless of the fact that you open different tranches at different times. Please correct me if I am wrong.
I watched many episodes of CD Trading Group 2 where Tom Hughes showed his live M3 trades in progression. His method is to enter every 2 weeks and maintain each campaign individually even for same monthly expiration cycles . I also remember that other M3 trader Vincent Lim from Singapore wrote on old M3 thread that he uses similar method as Tom Hughes.
After thinking about this, I suspect that you with your deep knowledge of this trade no longer look at your positions as a base M3 group but more look at individual components and have pretty good idea how each guy in your orchestra performs (depending on its position inside the tent or outside from greeks point of view ) if market goes up, down, volatility drops, rises , etc .
Would love to hear your opinion about pros and cons of yours and Tom's method.

Hi Sergei,

Yes, you are correct. I manage my position as a whole, although I enter butterflies in batches as and when funds become available as I close off my previous month's position. I know some others enter at different times for risk diversification. I did that in the past but I no longer practice that.

I'm not familiar with Tom's method but I'm assuming it's meant for risk diversification. I can see why that is logical and beneficial. However, it's not suitable for me. Due to the time zone difference, I try to minimize adjustments - doing adjustments usually means staying awake till 3am+. Having more individual trades in play means increased chance of adjustments. So I'll rather have one big position and if it gets hit, I'll deal with it but I end up with less days I need to stay awake.

Having said that, I do monitor different parts of my position individually. With the OV R code properly functioning now, monitoring individual parts has become very easy.

One common example - frequently I'll end up with a position with butterflies at 3 different strikes. If the market runs up (like now), quite often, although the greeks of the position as a whole is still healthy, but the lowest butterfly is so far away that it has become a drag to the position - ie negative theta and positive vega. It has turned into a dead wood. But if I only look at my position as as whole, such dead wood continues to be hidden.

Now that I screen through individual parts daily, I'll actively weed out them out. In the example - I'll either shift the butterfly up or if I still want to keep it there to provide the negative delta in case the market reverses, I'm better off just having a Put debit spread. In that case, I'll just remove the credit spread of the butterfly and leave the debit spread. In this way not only do I achieve my goal of having the negative delta, I have reduced the risk in case the market reverses because the credit spread is now gone.
 
One common example - frequently I'll end up with a position with butterflies at 3 different strikes. If the market runs up (like now), quite often, although the greeks of the position as a whole is still healthy, but the lowest butterfly is so far away that it has become a drag to the position - ie negative theta and positive vega. It has turned into a dead wood. But if I only look at my position as as whole, such dead wood continues to be hidden.

Kevin, Many thanks for your excellent as usual explanation. For my personal development I will keep daily track of greek changes for all my individual components. After you showed how to download this data from OV and process with pivot tables, it is very easy to do.
One more point I would like to clarify. Do I understand you correctly that in the end campaign getting closer to 21 DTE you are less likely reposition your whole "dead wood" butterflies but rather keep only the part of it which still has its purpose.
The reason for asking that question, I have a separate thread on CD where I post updates about my first live M3 Mar 16 trade.
Yesterday my vega turned positive and RUT was trading 25 points above my right long leg. With 17 DTE I decided not to roll up as I didn't like the prices of butterflies and T+0 line after possible adjustments. I decided to morph my position and to keep only credit spread 1000P/950P in order to continue getting a little bit more theta and exit completely by next Monday.
Is there a certain number of DTE when you stop maintaining your full position of 200 butterflies but simply start weeding out.
I assume it is a big difference for such situation with 45 DTE and 17 DTE . Thanks in advance.
 
Kevin, Many thanks for your excellent as usual explanation. For my personal development I will keep daily track of greek changes for all my individual components. After you showed how to download this data from OV and process with pivot tables, it is very easy to do.
One more point I would like to clarify. Do I understand you correctly that in the end campaign getting closer to 21 DTE you are less likely reposition your whole "dead wood" butterflies but rather keep only the part of it which still has its purpose.
The reason for asking that question, I have a separate thread on CD where I post updates about my first live M3 Mar 16 trade.
Yesterday my vega turned positive and RUT was trading 25 points above my right long leg. With 17 DTE I decided not to roll up as I didn't like the prices of butterflies and T+0 line after possible adjustments. I decided to morph my position and to keep only credit spread 1000P/950P in order to continue getting a little bit more theta and exit completely by next Monday.
Is there a certain number of DTE when you stop maintaining your full position of 200 butterflies but simply start weeding out.
I assume it is a big difference for such situation with 45 DTE and 17 DTE . Thanks in advance.

No definite DTE to start reducing position but usually it'll be around 21 - 14 DTE. It all depends on how the position is and whether I need to do an adjustment. I'd reduce as part of my adjustment.
 
Top
Contact Us