How to create an Excel table containing butterfly prices

Power Pivot and Power Query is a free Excel-Addin.
https://www.microsoft.com/en-us/download/details.aspx?id=39379

"Pivot on steroids" is powerBI Desktop. It is also free.
https://powerbi.microsoft.com/en-us/

I am currently working on it to get it done.
Watching a folder is very simple and self explaining. Thus the manual work of transferring data from OV to Excel is reduced to a minimum. Actually you just export the file to the folder and the rest will be done by PowerQuery. I have it done with 10 trading days. It updates automatically everytime you drop a new file into the folder. 4300 data rows are updated in seconds.

Afterwards you can use the imported data as usual in a pivottable or you use powerpivot (similar to powerBI). That would open the door for analysis of longerterm historical OV data. Thats why I have asked Len for a the following fatures of the export function (http://forums.capitaldiscussions.co...vue-feature-request-poll.379/page-6#post-4447).

Going forward you might decide to use powerBI (instead of excel). PowerBI has a new Syntax (its called Data Analysis Expressions (DAX)).
You will need some time to get used to it. But its similar to Excel formulas. it is a combination of a database AND excel. Very powerful stuff.

Beyond that there is only sql server 2016 in combination with SRSS ...
 
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.

Kevin, I would like to ask your opinion if my current Apr 16 M3 consisting of 2 lots looks healthy to you. As you said in your previous post, all greeks for a combined position look good but Vega is positive for lot 1 as RUT trades almost 40 pts outside right leg. I am still trying to decide if I need to adjust my lot 1 or not.
Thanks in advance.
Combined position of lot 1 and lot 2 looks as follows:
2016-03-14-12-00-comined-lots-1-2-png.997

Lot 1 only:
2016-03-14-12-00-lot-1-png.998
Lot 2 only:
2016-03-14-12-00-lot-2-png.999

 
Kevin, I would like to ask your opinion if my current Apr 16 M3 consisting of 2 lots looks healthy to you. As you said in your previous post, all greeks for a combined position look good but Vega is positive for lot 1 as RUT trades almost 40 pts outside right leg. I am still trying to decide if I need to adjust my lot 1 or not.
Thanks in advance.
Combined position of lot 1 and lot 2 looks as follows:

Hi SVL,

This is how I'd think about the issue :

1. Firstly, I'd look at the butterfly of lot 1 alone, without the call. I'd look at whether it is still contributing to the overall position or is it a drag. Butterfly's primary job is to provide positive theta in my opinion. If it isn't then it's no longer doing its job. In this case, I would consider making a change.

2. However, there might a different consideration - perhaps you might want it to provide the down side protection at least for a few days if you are of the opinion that the market might pull back. But then the question becomes is butterfly the best option to achieve that objective ? There are other ways to provide downside protection. Obviously there are trade offs that need to be considered.

3. Another thing I would look at - the individual spread of the lower butterfly - the Debit vs the Credit spread. Sometimes when market is so far ahead, the credit spread has no money left but we can't see that easily in the overall position. But if you isolate it, you can see that the credit spread only has risk and no more profit potential. It has no reason to exist anymore.

So there is no one right answer to your question. It all depends on what you want to achieve. My point is I'd go down to the fundamental question of whether each component of the trade is doing the job it is hired to do. If it is not, then adjustment should be made.

Kevin
 
Kevin,
Many thanks for looking into this and giving your very helpful advice. It also didn't make any sense to me to have $75000 risk on 990P/940P credit spread which I could eliminate entirely for a small debit. I played with different configurations and ended up with this remaining position as of 15:10.
2016-03-15-15-10-png.1000

It gives flat T+0 line within +/- 5% RUT move and I plan to get the second set of butterflies when my delta reaches positive 50 in either market direction.
Many thanks for all your help .

Sergei.
 
Kevin,
Many thanks for looking into this and giving your very helpful advice. It also didn't make any sense to me to have $75000 risk on 990P/940P credit spread which I could eliminate entirely for a small debit. I played with different configurations and ended up with this remaining position as of 15:10.
2016-03-15-15-10-png.1000

It gives flat T+0 line within +/- 5% RUT move and I plan to get the second set of butterflies when my delta reaches positive 50 in either market direction.
Many thanks for all your help .

Sergei.

Glad that you found a solution to the issue.

I started to look at my options position differently after I listened to Elon Musk talk about the concept of First Principle Thinking. Here's a good video on that subject


and another article http://www.game-changer.net/2014/06/26/first-principles-thinking-a-better-way-to-innovate/

Often we do something because it has always been done this or that way. First principle thinking goes down to the lowest granular level of truth and build up from there.

Then it suddenly dawned on me that I have been thinking by analogy (a term that Elon coined) when I just trade by following a trade plan. Applying the first principle concept to options trading, I really need to understand the fundamentals of how options and simple spreads behave and then build up our arsenal of tactics from ground up.

To take my game to the next level, I really had to dissect a complex position and decompose a trade plan to their basic units in order to start thinking from first principles and build from bottom new ways to trade. That's when I started to look at and monitor individual components of the trade and start asking myself the WHY instead of just following the HOW of a trade plan. The R code in OV by chance is a convenient way to do just that.
 
Kevin,
Many thanks for your very insighful post. Your contribution to this forum can not be matched. I hope you will never lose your desire to share your experience and knowledge with other students of market neutral option trading. It is very helpful to have a chance to ask questions and get such detailed replies from the person who knows Options in and out.
Sergei.
 
Kevin, I have started learning M3 about 2-1/2 months ago. April expiration is my 2nd cycle of live M3 trades. Right now my current M3 position has 2 DIMC and a combination of 14 short and 14 long puts. We have about 23 DTE and I am ready to start exiting this trade. Initially I started Apr expiration with 2 lots as per standard M3 guidelines but now I am not sure what should be my profit target for my current position ( for one or for 2 lots)? My intention is to remove 50 % tomorrow (but keeping 1 DIMC for May campaign) and to remove the remaining 50 % next week.
3-23-16-15-30-rut-m3-apr-16-png.1011

Also I would like you to ask a question how did you learn to remain patient and continue to sit tight with your flat T+0 line position. I almost made the adjustment yesterday when my delta was close to negative 40 but then realized that I should stay put and stick to the guidelines and make no adjustments unless necessary. I remember very well from the book "Reminiscences of a Stock Operator" that most of money are made from sitting tight and not trading when you have a winning position. "Man who can both be right and sit tight are uncommon. I found it one of the hardest things to learn. But it is only after a stock operator has firmly grasped this that he can make the big money."
How did you develop this invaluable skill for M3 trader?
Thanks in advance.
Sergei.
 
Kevin, I have started learning M3 about 2-1/2 months ago. April expiration is my 2nd cycle of live M3 trades. Right now my current M3 position has 2 DIMC and a combination of 14 short and 14 long puts. We have about 23 DTE and I am ready to start exiting this trade. Initially I started Apr expiration with 2 lots as per standard M3 guidelines but now I am not sure what should be my profit target for my current position ( for one or for 2 lots)? My intention is to remove 50 % tomorrow (but keeping 1 DIMC for May campaign) and to remove the remaining 50 % next week.
3-23-16-15-30-rut-m3-apr-16-png.1011

Also I would like you to ask a question how did you learn to remain patient and continue to sit tight with your flat T+0 line position. I almost made the adjustment yesterday when my delta was close to negative 40 but then realized that I should stay put and stick to the guidelines and make no adjustments unless necessary. I remember very well from the book "Reminiscences of a Stock Operator" that most of money are made from sitting tight and not trading when you have a winning position. "Man who can both be right and sit tight are uncommon. I found it one of the hardest things to learn. But it is only after a stock operator has firmly grasped this that he can make the big money."
How did you develop this invaluable skill for M3 trader?
Thanks in advance.
Sergei.

Hi Sergei,

You'll first need to work out the profit targets and exit timing as part of your trade plan and ensure that plan is properly back tested. Although we can be trading the same M3, but different M3 trade plan will call for a different decision on adjustments as well as profit taking. Therefore, it's difficult for me to say whether it's now an exit point for you.

On your question about being patient, I wish I could tell you that experienced traders are immune to emotional influences and are able to stick to trading plan easily. But that would be a lie. Reality is greed, fear and regrets are well and alive. The difference though is what is done in response to these emotions. Here's what worked for me :

1. Firstly - I back test everything to death to intellectually convince myself that the plan works. Only with that I can quiet down the subconscious whisper that pops up occasionally..... "maybe this time is different".

2. Then I try to reduce external noise as much as possible. One example is financial news. I used to pride myself in knowing everything that's happening in the market. I read financial news, watch CNBC, subscribe to newsletters, keep a calendar of major events, etc... My belief was being in the know would give me an edge. The reality turns out the opposite. News overload caused me to doubt and deviate from my plan. Now I have a rule to turn off CNBC and not read news on major events, eg. unemployment rate, GDP etc... 1 to 2 hrs before market opens and pay little attention to them in general.

3. Simplicity - I used to trade with 2 to 3 monitors with a dozen charts to monitor correlated assets, market internals and various technical indicators. Now I have zero. I know many people will disagree with me on this one. But my conclusion after using them for a few years is that these are noise that didn't help my trading while causing anxiety and deviation from plan. The simpler I made my trading, the better the performance. The less indicators I use, the more I am able to trade my plan.

4. The nifty new strategy - sometimes I learned a new trick, took a new class or read something online, I would be in a rush to implement it. I would find excuses to use the new adjustment techniques and to blend them into my trade plan prematurely, without giving it enough thoughts on their effectiveness. Again, this will cause doubts, anxiety and confusion which will result in poor performance. The point here is not that we shouldn't try new things but we need to be patient in properly back testing and understanding what we are getting into before weaving them into our plan.

Well.... these are my experiences. Not sure if they would work the same ways for others. But after I practiced them, I felt I was able to control my emotions much better and resulted in much more consistent results.

Hope that helps.
clear.png
 
Only with that I can quiet down the subconscious whisper that pops up occasionally..... "maybe this time is different". --- I am definitely guilty of this one, for me its a work in progress
clear.png
 
3. Simplicity - I used to trade with 2 to 3 monitors with a dozen charts to monitor correlated assets, market internals and various technical indicators. Now I have zero. I know many people will disagree with me on this one. But my conclusion after using them for a few years is that these are noise that didn't help my trading while causing anxiety and deviation from plan. The simpler I made my trading, the better the performance. The less indicators I use, the more I am able to trade my plan.

This also gives you a lot of time to do stuff that is meaningful namely research and development instead of watching the charts all day. I am still guilty with this point but have scaled down dramatically the amount of time I devote to "reading" market internals.
 
Kevin,
Many thanks for writing this great post. Maybe I was not clear enough but I was not asking for a specific advice when to exit from my current trade. I was more curious how would you interpret my current position : as a single lot or a double lot M3 ? This is crucial not only for profit taking but for adjustment decisions based on greeks situation .
I am still debating with myself if I would be better off not to adopt immediately your free style of trading of M3 strategy but rather to continue trading traditional M3 mechanically for the next several months and only after that to transition slowly to your way of trading M3. I feel intuitively that your way is much superior but probably I should not start running before I learn how to walk with confidence.
After watching butterfly prices for several weeks, I realized that M3 is like a net for butterflies . You have to follow the market patiently for a few weeks, make all needed adjustments to stay slightly behind the market and hope that the pullback happens within last 3-4 weeks when the theta starts kicking into the trade.
Once the butterfly is inside the tent during last 3 weeks, it starts growing big from $10-15 to $50 eventually. The only problem we do not know at what specific strike till the last day of expiration.
I really like my current Apr position as my current breakeven points at expiration are 1035 and 1120 which are close to current support and resistance points for RUT. Like you I am coming from a great admirer of Technical Analysis and all sorts of technical indicators to a pure simplicity. Now Point and Figure chart is all I need to look at.
2016-03-24-rut-pnf-chart-png.1012
 
Kevin,
Many thanks for writing this great post. Maybe I was not clear enough but I was not asking for a specific advice when to exit from my current trade. I was more curious how would you interpret my current position : as a single lot or a double lot M3 ? This is crucial not only for profit taking but for adjustment decisions based on greeks situation .
I am still debating with myself if I would be better off not to adopt immediately your free style of trading of M3 strategy but rather to continue trading traditional M3 mechanically for the next several months and only after that to transition slowly to your way of trading M3. I feel intuitively that your way is much superior but probably I should not start running before I learn how to walk with confidence.
After watching butterfly prices for several weeks, I realized that M3 is like a net for butterflies . You have to follow the market patiently for a few weeks, make all needed adjustments to stay slightly behind the market and hope that the pullback happens within last 3-4 weeks when the theta starts kicking into the trade.
Once the butterfly is inside the tent during last 3 weeks, it starts growing big from $10-15 to $50 eventually. The only problem we do not know at what specific strike till the last day of expiration.
I really like my current Apr position as my current breakeven points at expiration are 1035 and 1120 which are close to current support and resistance points for RUT. Like you I am coming from a great admirer of Technical Analysis and all sorts of technical indicators to a pure simplicity. Now Point and Figure chart is all I need to look at.

Oh I see what you are asking now. If you started with a 2 tranche M3 and exited 1 tranche, I would treat the remainder as 1 tranche and decide on adjustments based on 1 tranche greeks.
 
Kevin,

I started importing data into Excel from OV as per your video.

Once I had the pivot table finished and populated, I returned to OV to the enter the same butterfly (30 points below spot).

For one, the price is very different between OV and using (Bid+Ask)/2, but more important, the price direction movement is not the same:
In the third column, the butterfly price was up (12.75, +0.4) but down in OV (10.24, -0.42).
The same issue is to be found in column 5 (up in the sheet, down in OV), etc...

I can see how useful this would be for me to learn and observe price movement of butterflies, the questions is which numbers truly represent the price change ?

Thanks,
Jeff

upload_2018-7-31_15-5-31-png.2992
 
Hi Kevin,
I see that this thread was moved from CD to this website

Any chance that the the other thread about your YouTube video series about excel options trading will be moved here and perhaps continue where you left off or you still have a busy schedule ?
I really enjoyed that video series and learned a few things along the way
 
Kevin,

I started importing data into Excel from OV as per your video.

Once I had the pivot table finished and populated, I returned to OV to the enter the same butterfly (30 points below spot).

For one, the price is very different between OV and using (Bid+Ask)/2, but more important, the price direction movement is not the same:
In the third column, the butterfly price was up (12.75, +0.4) but down in OV (10.24, -0.42).
The same issue is to be found in column 5 (up in the sheet, down in OV), etc...

I can see how useful this would be for me to learn and observe price movement of butterflies, the questions is which numbers truly represent the price change ?

Thanks,
Jeff

Sorry not sure I understand your question. What are the two sources you're comparing? OV backtrader data vs what?
 
Hi Kevin,
I see that this thread was moved from CD to this website

Any chance that the the other thread about your YouTube video series about excel options trading will be moved here and perhaps continue where you left off or you still have a busy schedule ?
I really enjoyed that video series and learned a few things along the way

I'm slowing migrating threads that I want to keep. This was just the first. It's a tedious process.
 
Top
Contact Us