Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Logout MarketXLS
OptionsOptions strategies

How To Manage Your Option Trades (Explained With Template)

Written by Vishal Nayyar (Individual Contributor)
Tue Dec 15 2020
Managing & Tracking option trades
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Managing & Tracking option trades

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.

Managing option trades

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


Tracking option trades

Select the template area & press Ctrl+C.

Step 2

Tracking option trades

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

Step 3

Tracking option trades

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.

Tracking option trades

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.



Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Stock screens
Public saved screens

Top 100 Gainers Today

Top 100 losers Today

Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONSGet started today
Search for a stock

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

Top MarketXLS Rank stocks

Dick's Sporting Goods Inc logo

Dick's Sporting Goods Inc

Optionable: Yes
Market Cap: 8,757 M
Industry: Specialty Retail
52 week range    
Dillard's Inc. logo

Dillard's Inc.

Optionable: Yes
Market Cap: 4,506 M
Industry: Department Stores
52 week range    
EOG Resources Inc. logo

EOG Resources Inc.

Optionable: Yes
Market Cap: 61,798 M
Industry: Oil & Gas E&P
52 week range    
STORE Capital Corporation logo

STORE Capital Corporation

Optionable: Yes
Market Cap: 8,941 M
Industry: REIT - Diversified
52 week range    
National Research Corporation logo

National Research Corporation

Optionable: No
Market Cap: 983 M
Industry: Diagnostics & Research
52 week range    
Chesapeake Energy Corporation logo

Chesapeake Energy Corporation

Optionable: Yes
Market Cap: 11,105 M
Industry: Oil & Gas E&P
52 week range    
NACCO Industries Inc. logo

NACCO Industries Inc.

Optionable: No
Market Cap: 346 M
Industry: Coal
52 week range    
Marathon Oil Corporation logo

Marathon Oil Corporation

Optionable: Yes
Market Cap: 14,494 M
Industry: Oil & Gas E&P
52 week range    
Destination XL Group Inc. logo

Destination XL Group Inc.

Optionable: No
Market Cap: 340 M
Industry: Apparel Stores
52 week range    
CF Industries Holdings Inc. logo

CF Industries Holdings Inc.

Optionable: Yes
Market Cap: 19,103 M
Industry: Agricultural Inputs
52 week range    

More Features