A lot of the beginners are not able to make sense of some of the functions or components of a strategy that online tracking platforms offer, especially when it comes to options. Its better to always start with something that is easy to understand and that can be customized, as and when the need arises. MarketXLS ticks all these boxes. In this article I will be explaining, with the help of a template, how you can manage and track your option strategies in Excel using custom MarketXLS functions. The template used here is one that my colleague made while trading options and I suppose this could be of great help when you are just starting with options and want to apply strategies while trading. So without much blabbering, lets jump jump right into it.

Let’s look and try to understand the screenshot that I took from the template. There are basically two section on the template. On the left you add any new position you enter or any existing position that you want to change & on the right are the real time updates that you can monitor (for your active positions).

As you might have observed by now, I have entered into a credit spread here with the underlying as Apple stock. With =optionsymbol() in cell B5 I can easily give my option a name by using different attributes like strike price, whether it’s a call or a put option, its expiry date, Underlying stock & whether you are long or short on the option. Now you might be noticing the “Got@Open” column, well that is simply the option contract premium per option contract*the number of contracts bought/sold. I bought a total of 5 contracts for apple, paying on them a total of $400 as premium. Also, I sold 5 AAPL option contracts for $685 with the same expiry but with a higher strike price of $110. This technique, is a credit spread and I put this practice to use because I assume the market is bullish. Essentially, the difference of the premium amount i.e. $285 (685-$400) is the highest amount that goes into my pocket in case both the options expire worthless.

Moving on to the right section of the template, we see days to expire which tells us exactly how many days it will take for the option contract to expire. I have simply used the formula =DAYS360($A$2,E5) here. Then we have the next column with “Last (stk)” which basically tells us the last strike price for the underlying stock ( formula used here is =Last(F5) ). The Ask and the Bid price are essentially one of the most important things that an options trader monitors and with MarketXLS you can get the real time value for them by just typing in the formula =qm_stream_ask() for the ask price and =qm_stream_bid() for the bid price. One more important thing to note here is the “Req/Got at close” column. This was made using the formula =IF(G5=”Write”,M5*100,100*N5)*J5. This means in case I earlier wrote a Put option, In order to close it I will now have to buy it at the Ask price and vice versa In case of buying a put option in the first place. This column shows the money required or the money we get while closing the position. Finally, we have the gain or loss shown in the last column which is the sum of money we got/paid at opening the position and the money we require/got at closing the position . When we sum up gains and losses from the individual positions (as shown in cell V12) we get the final profit/loss we made on our strategies using different positions.

Tracking your positions

One can easily track his/her positions simply by copy and pasting the data as values beneath the main template during the life of the option contract. This will help one to know where his final Gain or loss stands at a given point of time and one can also see how the different measures used in the template changed during the life of the option contract. To do so, I have explained below, step by step to go about the process

Step1

Select the template area & press Ctrl+C.

Step 2

Go to the Home Tab and in the Paste dropdown select Paste special.

Step 3

Select Formats radio button and click OK

With this I suppose you now have an understanding of how MarketXLS provides value with its wide variety of custom functions. This template is just one of the many templates you can make to fully unlock the potential of the MarketXLS. I hope you understood the above mentioned template and with this I wrap up this article. Also, i am adding below a screenshot of the positions that I tracked till date.

PS: Some of you might be wondering the analysis that went behind choosing the credit spread strategy here. Well, I simply didn’t do much of the background research myself on the stock. I have simply found the analysis from one of the websites that I came across recently (It stated that the stock price will rise so I put the credit spread in place). Also, I am not adding in this template the other complex measures like Implied Volatility, Vega, Delta & Theta but I encourage you to understand the basics and then play around to find and add these measures in the subsequent options template that you make.

None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person.

The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein.

the article is written for helping users collect the required information from various sources deemed to be an authority in their content. The trademarks if any are the property of their owners and no representations are made.