Option Contracts
Options, Options strategies

Options Trading For Beginners (Management And Tracking)

Options Trading for Beginners

As a beginner options trader, learning how to trade options can be an exciting experience, but it can also be extremely frustrating and very costly. There are some common mistakes that lead to many beginner options traders to lose large sums of money in a very short period of time. Here’s a brief overview of options trading that cuts through the jargon and gets right to the core of this versatile way to invest.

Step by Step Guide for Beginners on Options Trading

1. The first step is to trade with a commission friendly brokerage firm because trading options actively can be extremely costly, especially when paying high commission rates and ticket charges.

2. Now, the second tip is to pay attention to risk. So as a new options trader, it’s very easy to fall into the trap of just focusing on how much money you can make, but ignoring how much money you can lose with any particular strategy. So just keep in mind that in the long run, not making as much money is always better than losing a lot of money.

3. Now, our third option trading trip is to stick to well-known stocks, so a lot of the time new traders will look for obscure companies or they’ll trade penny stocks. They’re going to have a higher probability of staying around longer and they’re just going to be less surprised overall. So just stick to well-known companies and ETFs that have lots of trading volume and very liquid options.

4. Now, our fourth option is trading tips for a beginner is to avoid complex products. So if you remember, XIV XIV was a complex volatility product that gained a lot of popularity among retail investors before losing 95 percent of its value overnight. So don’t trade something unless you’re absolutely sure you understand how it works. Now, if you want to be safe, just avoid products with 2x 3x short leveraged inverse or ultra in their names or descriptions.

5. Our fifth options trading tip is going to be to stick with limited risk strategies. So the reward potential of high-risk strategies like short straddles and short strangles can be very appealing at first. But they can also leave you broke or in debt in one losing trade.

A lot of people don’t like to think about psychology because they don’t think it matters. But the truth is that trading is stressful and being involved in the markets, in general, is stressful. To learn how you react to profits and losses over time and use the strategies that are easiest on you psychologically. Options Trading for Beginners

Develop a quantifiable strategy or plan

A training plan should include the stock or product to be traded, and it should include a specific option strategy with a quantifiable setup. Options traders use the Greek Alphabet to reference how option prices are expected to change in the market, which is critical to success when trading options.

The most common ones referenced are Delta, Gamma, and Theta. Although these handy Greek references can help explain the various factors driving movement in option pricing and can collectively indicate how the marketplace expects an option’s price to change, the values are theoretical in nature.

Now, for example, that could mean selling thirty Delta put in buying the sixteen Delta put to create a put spread with thirty days to expiration. The next things you need are consistent trade sizing and quantifiable entry, exit, and adjustment triggers. So you know exactly what you’re doing with your trade at all times.

Overview of Options Trading for Beginners 

Options are contracts that give the owner the right to buy or sell an asset at a fixed price for a specific period of time. Fortunately, there are only two types of standard option contracts: a call and a put.

Options Trading for Beginners

The most common underlying securities are equities, indexes, or ETFs (Exchange Traded Funds). As an individual trader, you really only need to concern yourself with two forms of volatility: historical volatility and implied volatility.

Historical volatility represents the past and how much the stock price fluctuated on a day-to-day basis over a one-year period. Implied volatility is one of the most important concepts for options traders to understand because it can help you determine the likelihood of a stock reaching a specific price by a certain time. Options Trading for Beginners

Those are just a few of many commonly used words you’ll hear in a room full of option traders. Simply put, it pays to get your terminology straight. That’s why we decided to create an options trading glossary to help you keep track of it all.

The best thing you can do before you fund your account and start trading is to clearly define your investing goals. However, this does not influence our evaluations. Our opinions are our own. Options can provide flexibility for investors at every level and help them manage risk.

Read More
Implied Volatility Surface for AAPL
Options, Options strategies

Volatility Surface Chart (It’S Impact On The Profitability Of Option Trades)

Volatility surface is the plot that can help you determine the best combination of the strike and the expiry date to maximize the profits on an Options Trade.


Volatility is basically the fluctuation in the price of a product or a derivative. The more is the swing, the more is the volatility. There are many factors that cause volatility in the market, major ones being the increase in market participants and their anticipation of the market in the near future. Volatility is a very useful tool, especially for trading options. The profitability of an options trade revolves around volatility. However, an option seller is better off only when there is less volatility. But, volatility is also used by sellers to hedge their funds against sharp falls in stock prices.

For example, an option buyer will profit only from a huge move in the stock price as he has to pay a premium to the seller and he will have to earn at least equal to the premium to breakeven. We will discuss different types of volatility and how an options trader can gain from volatility in the market.

Volatility surface

Types of volatility
Volatility is broadly divided into two parts, historical and implied. Historical volatility is calculated on past data. It can be calculated using basic statistical tools like Standard deviation and variances in excel. But for a trader, historical data is bare of any use, and only the ability to forecast future volatility would help a trader to design strategies to maximize profits. And, this forecasting of future volatility is called implied volatility. It doesn’t give the exact data about volatility but it gives a rough idea to start with.

Implied volatility is calculated based on the news, good or bad that is to occur during the lifetime of an option. It also takes into account the current stock price and the strike price of the option.

Black-Scholes model (BSM)
This particular model cannot be overlooked while talking about volatility. It was a formula developed by three economists – Fischer Black, Myron Scholes, and Robert Merton in 1973. It was developed to calculate future option prices based on call option price, current stock price, strike price, risk-free rate, and time to maturity. But it had its own drawbacks. Firstly, it is only applicable to European markets, where the options can only be traded on the date of expiry, while in US markets an option can be traded prior to the date of maturity.

One more shortcoming of the model was observed after the “Black Monday” stock market crisis in 1987. The volatility of the same options was varying while BSM assumes the volatility to be constant for the same options while calculating Price. It was observed that the same options with different strike prices or maturity showed different changes in volatility. This gave rise to a new concept “Volatility smile” or “volatility skew”.

Volatility smile
It derives its name from the fact that when implied volatility is plotted against various strike prices the graph looks like a smile. The implied volatility is far from being flat which contradicts the BSM model. From the image below, we can say that implied volatility is high when the price is far from the strike price and decreases when the price approaches the strike price from either side. But it doesn’t always work this way.

Graphs are found to be skewed to their left or right based on market sentiments.

Volatility skew
volatility skew arises when the graph is not symmetric and is skewed towards left or right. Here we will discuss only left skew as it is more commonly seen in the markets, which is also known as reverse skewed.

Reverse skew happens when the IV is more on the put side because most of the options buyers are anticipating the stock to go down. There can be many reasons like news and the fact that most options traders are long on stocks and they want to hedge against a possible fall in the stock price. Forward skew is exactly the opposite.

Volatility surface
We have already seen how IV reacts to various strike prices. When we include the expiry date, we get a 3-dimensional structure which is known as the volatility surface.

A volatility surface is basically a plot to examine the best possible scenario based on the strike price and expiry date for the maximization of profits from an options trade. Normally, Options with a shorter time to maturity have multiple times the volatility compared to options with longer maturities.


AAPL.png”>AAPL.png” alt=”Implied Volatility Surface for AAPL” width=”1067″ height=”671″ />

Figure 4: Plot of IV against days to expiration and strike price.


To sum up, there are three major parameters an options trader should look into while buying or selling options which are implied volatility, strike price, and time to expiry. A good combination of these three can not only help a trader maximize his profits but also hedge his funds against huge losses. Normally a short expiry period and a strike price far from an anticipated price are preferred.

There are many strategies based on implied volatility. Some of them are listed below:
● Call spreads
Put spreads
● Long straddle
● Strip straddle
● Short condor spread
● Short butterfly spread
● Short albatross spread


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.


Volatility smile and skew, here.

visualizing the volatility surface, here.



Read More
Unusual Options Activity Benzinga
Options, Options strategies

Unusual Options Activity – How To Identify, Act And Track For Success


A common investment theory is that securities that are owned by institutional investors tend to trade with a closer correlation to the financial performance of the company or, in the case of futures contracts, with the overall economy. Traders, therefore, will frequently look at volume and implied volatility to determine future price direction. 

Investors look at call option volume along with the price to determine whether to take a particular stock position. In this article, we’ll look at what does the volume and Open Interest signify and what are the unusual option activities. We’ll also go into detail about how to identify the unusual options activity and what precautions need to be taken.

Volume and Open Interest

Volume and open interest are two key technical metrics that describe the liquidity and activity of options and futures contracts. “Volume” refers to the number of contracts traded in a given period, and “open interest” denotes the number of contracts that are active, or not settled. 

Let’s look at the market signal and the inference that can be derived by comparing the increase/ decrease in volume and open interest in the option price. 

Price Volume Signal Inference
Up Up Bullish Indicates at the strength in the up move, and that the move is likely to continue
Up Down Cautiously Bullish Indicates at weak buying pressure, due to which rally could run out of steam
Down Up Bearish Indicates the strength in the down move, and that the move is likely to continue
Down Down Cautiously Bearish Indicates weak selling pressure, due to which the decline could soon run out


In short volume that confirms the price movement indicates the current trend will likely continue whereas volume that does not confirm price action indicates the trend is on a weaker footing.


Price Open Interest Signal Inference
Up Up Bullish Indicates a new long position is being built and the uptrend in price is likely to continue
Up Down Cautiously Bullish Indicates at short covering and the increase can halt once short-covering ends
Down Up Bearish Indicates a new short position is being built and the downtrend in price is likely to continue
Down Down Cautiously Bearish Indicates at long unwinding and the decline can halt once long unwinding ends


In short open interest that confirms the price movement indicates the current trend will likely continue whereas open interest that does not confirm price action indicates the trend is on a weaker footing.


Understanding the theory behind options volume

Trading options based on volume assumes that the trading activity is being done by informed stock traders. 

If the trader is anticipating good news about a stock, such as an earnings report that is expected to beat analysts’ expectations, then they will look to buy call options. Since the trader is anticipating that the share price will be increasing, they would be fine with buying the stock outright. However, the options contract will allow the investor to leverage their buying power.

On the other hand, if the trader is anticipating bad news about a stock, they will look to sell call options. This means that they are hoping to find buyers that have a different opinion of the stock who will pay them a premium to buy their call option.

As you can see, options volume is a complementary trading signal. If there is a volume on call options that is accompanied by a rising price for that call option indicates that professional traders believe the price of the stock is going to go higher. If there is a high call option volume that is accompanied by a declining call option price, it is a signal that there is speculation that the stock price will go lower.


Three Ways Options Activity Is ‘Unusual’

  1. One-way options market activity can be considered unusual is when the volume is exceptionally higher than its historical average. 
  2. Another sign of unusual activity is the trading of a contract with an expiration date in the distant future. Usually, additional time until a contract expires allows more opportunity for it to reach its strike price and grow its time value. 
  3. Contracts that are “out of the money” are also indicative of unusual options activity. These trades are made with the expectation that the value of the underlying asset is going to change dramatically in the future, and buyers and sellers will benefit from a greater profit margin.

How is it useful to know the unusual options volume?

The unusual options volume can help traders in many ways, including:

  • Focusing on where there are potential trading opportunities. Traders will look for opportunities when the market value of an option diverges far from its expected value. The unusual trading activity could push option prices to overvalued or undervalued levels.
  • Unusual option volume can alert traders if something notable is happening in a particular stock, sector, or within the market as a whole. By reading the options volume, stock price, and implied volatility, traders can get an insight into how the market is feeling about the stock.

Identifying Unusual Options Activity

The first prerequisite for unusual options activity is that the total volume must be multiples of its average daily volume, typically at least five times the average daily volume. The higher, the better. 

While any option that is trading a high relative volume is worth taking a look at, we want to pay extra attention to the options that make large individual orders.

This indicates that one trader or institution is making a bet, suggesting that someone might have some insider knowledge. I consider a “large individual order” to be about the size of one day of average volume.

There are many screeners that scan for unusual options activity, and most trading/charting platforms have this capability built-in. Benzinga Pro is one such platform. 

Unusual Options Activity Benzinga

Need to be cautious

It’s essential to keep in mind that, frequently, unusual options activity is due to a large hedge taking place, rather than someone expressing a directional view.

In addition to the possibility of an unusual order simply being a hedge, the order could also be a closing order. 

Most of the time, a closing order is somewhat easy to sniff out because there is usually an opening or closing marker placed on orders by the exchange or broker. However, this isn’t always the case.

In these situations, the way to decipher a closing order from an opening order is by observing the open interest. When volume is significantly higher than open interest, the chances are that the large order was an opening order. 

Using unusual options activity as a strategy

Unusual options activity is an advantageous strategy that may greatly reward an investor if they are highly skilled, but for the less experienced trader, it should remain as another tool to make an educated investment decision while taking other observations into account.

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.





Read More
Charts, Portfolio analysis and optimization, Portfolio Management

Yahoo Portfolio – Export Portfolio In Excel (To Use With Marketxls)

Yahoo Portfolio is a great way to keep track of your investments and stock purchases online. Yahoo Finance portfolio has a great and intuitive way to add your transactions and then track your portfolio in real-time.

Yahoo Portfolio

And with the “Create New View” screen you can simply add many more parameters in the table and compare the detailed performance of each stock in your portfolio.


Yahoo finance portfolios provide a very convenient way to export your portfolio in Excel as shown below.

yahoo portfolio

With one click now, you have your portfolio in Excel.  The downloaded file looks something like shown below.

yahoo portfolio

At MarketXLS, we have created a utility and template which takes and this input and turns yahoo portfolio into a portfolio analytics dashboard in your Excel using MarketXLS’s portfolio analytics functions.

A portfolio is essentially a range of cells that has the stocks in your portfolio and the corresponding proportion of stocks as shown below.

The total of all the weights in the portfolio should need to be 100% for these functions to work. These portfolio functions should work for all US & Canadian stocks and ETFs.

The range B3: C11 which is highlighted in the image below is what is the portfolio input.

We have a category for portfolio functions. You should see this in Excel’s functions browser.

In all MarketXLS portfolio functions, a portfolio range is the first and the only required input. The default time period we use for all our calculations is 12 months and all the results are calculated with monthly returns. The number of periods can be modified by providing an optional integer value after the portfolio as shown below.

=monthlyReturns(“Portfolio Range”,18) will return monthly returns for 18 months.

We would recommend downloading the presentation for these functions and further details around calculations and inputs to these functions.

efficient frontier calculator


Read More
Others, Stock investments, Stocks

Stock Vs. Stock Cfds: Which One Should You Trade?

There’s more than one way to gain exposure in and profit from the stock market. Investing in stocks as the primary asset is one, and trading contract for differences or CFDs is another. Stock trading and stock CFD trading each come with their own sets of risks and rewards and are suitable for different kinds of traders.

Stocks and Stock CFDs: What’s the Difference?

Having a working knowledge of stocks is the first step to understanding CFDs. You observe the same market which means you can use the same analytical tools such as stock volatility calculators. The difference between stocks and stock CFDs is ownership. When you buy a stock, you effectively own a share of that company that you can sell later on.

A CFD, on the other hand, is a financial derivative that doesn’t give you ownership of the primary security. Instead, what you buy is a contract between you and another party — generally a broker — based on a prediction you make of the stock’s price movements, which can go either way.

Typically, stock investors buy shares they think will appreciate in value, which is also known as going long. But, say you think the stock prices of company ABC will decline. As a stock trader, you would want to opt out of buying ABC stocks to avoid suffering a loss. Or, you can short ABC stocks you already own (more on that later). A stock CFD trader has the option to bet on said decline and net a profit from the price difference in the value of the stock. And because you don’t actually own the underlying asset, you don’t risk losing to the negative price movement.

Advantages and Disadvantages

Shorting: Traders have the option to go for a long or short sell. The first simply means you purchase stocks now to sell it for a higher price later on. It’s a common strategy for long-term or passive investing. The process of short selling stocks is a little bit more complicated — you’re essentially borrowing overvalued shares to sell and buying it back later at a much later rate. Taking the short position is much simpler for stock CFDs as seen from the ABC company example. The steps are similar to taking the long position and easier to understand and visualize, except your prediction of the stock value is the opposite.

Leverage: Stock CFDs can be traded on margin, or using leverage from a broker. These are borrowed funds to increase your exposure to the market that can allow you to multiply profits and enter other trades using limited capital. However, leverage also risks magnified losses. In contrast, stock trades require upfront payment so you don’t risk losing more than you started with.

Costs: Stock trading fees such as brokerage fees and trade commissions can eat into your returns. While those are also true for CFDs, trading stock CFDs are exempt from stamp duty in the UK and Ireland. This allows traders to save on 0.5% tax rate on owning securities, which doesn’t apply to owning financial derivatives like CFDs.

No ownership: Not having ownership to the asset means you aren’t exposed to the risk of falling stock prices. The biggest drawback though is losing out on rights granted to shareholders of a company. These rights include sharing in the profitability as partial shareowners, influence to the decision-making process of the company, and voting.

Counterparty risk: Another major disadvantage of stock CFD trading is the heightened counterparty risk compared to stock trading. This is because you’re dealing in derivatives instead of the primary security. A CFD provider can default or fail to deliver on their promise to you resulting in lost capital. That’s why being selective with a broker is a must as there are some unregulated providers.

Which one should you trade?

Stocks and stock CFDs each carry their own set of risks and rewards. Though stock trading is by no means easy, it doesn't require as much experience as stock CFD trading. Share trading also grants investors certain rights that CFD traders don’t get. And you can take a long-term and passive approach to stock trading.

With that said, stock CFD trading is unique in that you can actually profit from falling share prices. Leverage can magnify your profits on a limited trading capital and you can enjoy lower fees. Stock CFDs are more suitable for active traders and short-term investing.

Read More
Getting Started, How to use MarketXLS, Release Notes

Marketxls New Release Version 9.2

Key Updates in this release


New Portfolio Analytics Functions

In this release we have added very advanced portfolio analytics functions. All these functions take a portfolio as an input and then return results. The results can be a number or result data table. 

A portfolio is essentially a range of cells that has the stocks in your portfolio and the corresponding proportion of stocks like shown below. 

The total of all the weights in the portfolio should need to be 100% for these functions to work. These portfolio functions should work for all US & Canadian stocks and ETFs.

The  range B3:C11 which is highlighted in the image below is what is the portfolio input. 

We have created a completely new category for portfolio functions. You should see this in the Excel's functions browser. 

list of portfolio functions

In all these new functions a portfolio range is the first and the only required input. The default time period we use for all our calculations is 12 month and all the results are calculated with monthly returns. The number of periods can be modified by providing an optional integer value after the portfolio like shown below.

=monthlyReturns("Portfolio Range",18) will return monthly returns for 18 months.

We would recommend downloading the presentation for these functions and further details around calculations and inputs to these functions. 

Download the presentation here.

Download the Portfolio Template here.

Please also review some of these blog posts we have put together to explain the functions and associated calculations


Stock News - Why is it Moving? and Why it Moved? 

We have partnered with Benzinga to deliver current and historical news functions in MarketXLS in this release. The news subscription is available as an additional optional addon data bundle and can be purchased from here.

We will provide more updates about this news addon in coming days.


New Templates

We have curated some user generated new templates. If you also have some MarketXLS Template which you want to share with the community, please contact us here.

You can download these templates from the link below.

Portfolio Analytics Template

Options Filtering Template

Options History Template

Real-time Options tracking Template

Short Analysis Template

New Template (requires Benzinga's new Addon for MarketXLS. See here)


Other updates and fixes

We have worked on and have fixed many issues like following

1) Optimizations in Refresh algorithms

2) Quick stock chart optimizations

3) Other items as reported in the helpdesk.


What are we working on for the next release

We are working on the following on the new upcoming release.

1) Integration with Interactive Brokers to be able to download portfolio

2) More updates on the Benzinga's news functions

3) New stock chart functions

4) New template for Technical Indicators like MACD, Stochastic and many more

Read More
Calculators, Fundamental analysis, Stocks

Stock Volatility Calculator

In this article, we will look at how we estimate the volatility based on the history of a stock price movement. With MarketXLS, you can simply use the stock volatility function to turn Excel into the stock volatility calculator.

Volatility comes in various forms and what MarketXLS volatility functions return is the simplest form of volatility. It is calculated by first calculated by the daily return on the stock and then taking the standard deviation on those daily returns. It can serve you well when you are comparing multiple stocks in your portfolio for its volatility.

The example below shows you a simple volatility comparison table of the stocks in a hypothetical portfolio.


MarketXLS Stock Volatility Functions

MarketXLS provides you with the following functions to quickly calculate the stock volatility without you having to calculate the returns, or download historical stock prices. We perform all those calculations on the MarketXLS server and all you have to do is use this function to get the value.

stock volatility calculator

Some functions have a pre-defined period like seven days, 15 days, nine months, and so on. But you can also use the Stock Volatility Custom dates function to calculate the volatility on the stock returns over a custom historical period as shown below.

MSFT.png” alt=”stock volatility formula” width=”451″ height=”162″ />

Just like, the volatility functions MarketXLS has other functions to calculate the cumulative stock returns using the functions like =StockReturnSevenDays(“MSFT“)

MarketXLS provides you with hundreds of custom functions to help you quickly analyze the stock data. Use these functions as your stock volatility calculator in Excel. Stock volatility simply refers to the severity with which the price of the stock fluctuates on a daily basis. And when comparing various investments this measure could be very useful in making investment decisions.

Beta vs Volatility

Both beta and volatility are measures of risk, sometimes these two may be confused. For example, a stock may have a higher beta and lower volatility when compared to other stock. The main difference between the two is that volatility is the total risk (Market Risk + Firm-Specific risk) whereas the Beta is just the market risk. So, the Beta will tell us the percentage change in the stock return given a one percent change in the return of the market.

Similar to the stock volatility functions you can also use MarketXLS’s CustomBeta functions to calculate the Beta on the stocks.

Stock Beta Functions

Read More
Getting Started, How to use MarketXLS, Stocks

Real Time Stock Quotes In Excel

MarketXLS is the best way to stream reliable real-time stock quotes in excel. In this article, I will show you how you can use hundreds of MarketXLS formulas and custom functions to stream real-time live prices of stocks in your Excel sheets.

How does it work?

Once you install MarketXLS, a new menu will show up in Your Excel with many new functions that are added to Excel. You can use the function like =qm_stream_last(“MSFT“) or =qm-stream_last(B2) where cell B2 is the one that contains the stock symbol. As soon as you do that, the stock prices will start showing in your Excel cell. Not only the last price, but you can also get the full quote of the stock using the functions like =qm_stream_ask, =qm_stream_bid, and so on.

These functions not only work with stocks but also can work with stock options, currencies, and any other ticker symbol you have access to.

real-time stock quotes in excel

How many ticker symbols can you stream in real-time?

The streaming data support up to 300 ticker symbols for simultaneous streaming. MarketXLS has other functions, which are the “refresh on-demand” functions. Refresh on-demand functions call the real-time stock prices in your Excel and require a click on the refresh button on the MarketXLS menu.

Unlike the streaming function, you can refresh more than 300 stocks or options at one time. If, for some reason, you would like to stop streaming and see a snapshot of quotes at a point in time, simply uncheck the streaming checkbox, as shown below.

live stock prices in excel


The data will still be a live snapshot of the prices from the market. If you are looking for a way to quickly get the live stock quotes in Excel with a very reliable source, then look no further.

We are so confident about the streaming real-time stock quotes in excel functions that we provide a 30-day money-back guarantee with MarketXLS. The stock price quotes will always match the price that you see in your brokerage accounts, and you can rely on the numbers.

We have performed many optimizations to make sure that the flow of the stock quotes is seamless. And also that the plugin does not hinder your ability to perform other tasks Excel.

Check out MarketXLS today The #1 Excel-based Investment Research Solution for Serious Investors

MarketXLS works with any version of Excel like Excel 2007, 2010, 2013, 2016, 2019, and Office 365. MarketXLS, at this time, only works with Windows-based machines.


Read More
Search for a function MarketXLS
Getting Started, How to use MarketXLS, Release Notes

Marketxls New Release Version 9.1

Key Updates in this release


New Function Search Utility

To make it easier for you to search for a function and get the details on it, in this release, we have a new Search and Help button on the extreme left of the MarketXLS menu. So, instead of browsing through the Excel-based function list, we will update this form with example usage, function names, and other details. We have more than 600 custom functions now.

Click on the Search and Help button on the MarketXLS to open the search form. You can search for a function like "dividends," "options," "ETFs," and so on.

Search for a function MarketXLS

New ETFs data and functions category

In this release, we have added the following functions in MarketXLS, which can give you detailed information on the Holdings within various ETFs. Alongside the holdings within the ETFs, other data like ETF Fund Family, Net Assets, Total Assets, etc. are also available.

Stock ETF functions

New Stock Volatility Functions

We have added the following functions in this release, which will get you the historical volatility on stocks. These functions are the standard deviation of historical daily stock returns. We calculate the volatility on all US and Canadian stocks and ETFs after every day's close.

The calculation is the standard deviation of the daily returns of a stock.

Stock Volatility Functions

Optimizations on Real-time streaming functions, Refresh All, Refresh Selected Buttons

1) The system will not automatically recognize a changed symbol in a cell and will start streaming the real-time data for the new ticker symbol, instead of needing to click on refresh buttons. 

2) The system will understand if the value of a result is nothing, and then it will return NA instead of a message.

3) Various other minor optimizations on the way the streaming data works, to make it faster.

4) New Streaming On-Off checkbox for you to quickly turn off the streaming data if you need to.


Options Filter function

This function will allow you to quickly filter out the option chain for the strike prices and expiry dates. qm_list function can also be used for this purpose. But this is a quick and easy way to get to the right options.  

As an example the following function call will return all the contracts from the option chain where the strike price is 10% above the current price. And the expiry dates are between 2020-06-30 and 2020-08-05

=QM_OptionChainFilter(A1,Last(A1)*1.1, , "2020-06-30", "2020-08-05")

Edit your caption text here

Read More
covered call example strategy using Excel
Option Strategies, Options, Options strategies

Covered Calls – What They Are & How You Can Profit (With Marketxls Data)

Wondering what covered calls are? In this article, we are going to tell you what a covered call is, when you would use it, and how MarketXLS can help you in applying the strategy.

What are Covered Calls?

Covered calls are a bullish investment strategy. You would usually consider applying the covered calls strategy when you expect that a stock price might rise, yet you are not too bullish on the stock. At the time of writing this article – in the midst of the coronavirus pandemic, this strategy is all the more relevant. Maybe you estimate that the price of the stock has bottomed out and will rise, but you are wary of taking an unprotected long position due to the market’s unpredictability. Applying the covered call strategy is a way to reduce risk.

Applying the covered call strategy involves making two separate trades:

  1. buying the selected stock and
  2. selling an “out of the money” call option on the same stock.

A Covered Call Example

Let us see how we would apply the strategy on Microsoft (MSFT) shares.

Step 1:

Using the well-known MarketXLS function =Last(“MSFT“) I can see that, at the time of writing the article, a MSFT share was last traded at $165.52.

Step 2:

Next, I need to import the MSFT options data into an Excel sheet. You can do this by selecting a cell inside the spreadsheet, in which you have entered the ticker MSFT, and then selecting “Get Option Chains” from the “Utilities” drop-down menu in the MarketXLS tab in your Excel ribbon. We need to select “Option Chain Out of The Money”. (Note: to access options data, you will need the additional QuoteMedia data subscription.)

excel covered calls strategies

This will import 27 columns of options data into your spreadsheet. For the purpose of this article, I have hidden the columns which we won’t be using at this time. I would suggest adding the Excel’s “filter” buttons to column headers, so that we can focus exclusively on the call options and to be able to sort them as desired.

The resulting overview, filtered, looks like this:

filter covered calls options in MS Excel

Step 3:

We are interested in making some money by selling a call option on MSFT, so I will sort the filtered call options from the most expensive one to the cheapest one. Top results now look something like this:

sort covered calls options in microsoft excel

Step 4:

If you are new to options, you may need to be informed that, while option prices are depicted as prices for 1 option for 1 underlying security (in this case, a MSFT share), options are traded in lots of 100. So, the latest price of $31.5 for the option at the top of our list, @MSFT  220916C00165000, means that you can sell a lot of 100 of these options and receive a premium of $3,150. The strike price for these options is $165, very close to the latest MSFT stock price. The reason why anyone would buy this put option is that, for them, it represents a guarantee that, whatever happens, they will be able to buy MSFT shares for no more than $165 per share, and they have this guarantee until September 16th, 2022, which is the expiry date.

Step 5:

For our example explanation of the covered call strategy, we don’t want to be tied in the option contract for so long. Filtering the above table further, to show us only options which expire in the next three months, I get the following list, with the option contract that I have selected marked blue.

covered calls example strategy using Excel

How Taking a Short Position in the Options Contract Looks

By taking a short position in the options contract @MSFT  200619C00175000, we can immediately receive a $605 premium ($6.05 per option * 100 options in a lot). To complete the covered call setup, we need to buy 100 MSFT shares, for $16,552. This results in our net cash position at:

$605 – $16,552 = -$15,947

Gain or Loss?

What have we accomplished with this setup and how much do we stand to lose or gain?

  1. In our bullish strategy, we have lowered our cost basis and reduced the risk from the stock price decreasing

Not considering the broker fees which your real-life trades inevitably incur and which you need to take into consideration, selling this call option moves our break-even point to MSFT share price of $159.47. This is because even though we have entered a long stock position, by buying MSFT at $165.52 per share, we have already received the option premium of $6.05 per option. As long as MSFT stock price remains above $159.47, our net cash result for these trades remains positive. Even if the stock price drops to, say, $150 per share by the expiration date, June 19th, 2020, our loss will be lower than if we did not enter the options trade, as shown below.

Without the option trade, buying 100 shares:

-$16,552 (initial expense from buying 100 MSFT shares) + $15,000 (selling the shares at a loss) = -$1,552

With the option trade, covered call strategy, buying 100 shares:

-$16,552 (initial expense from buying 100 MSFT shares) + $605 (received options premium) + $15,000 (selling the shares at a loss) = -$947.

  1. Assuming that our assumption pans out, we have received additional income

Keep in mind that our estimate for MSFT shares, which prompted us to enter the covered put strategy, was moderately bullish. We expect the stock price to rise a few dollars per share, not to soar 20% or more.

By buying the share at $165.52 and selling a call with a $175 strike price, our potential income maxes out if the options expire at the money, with MSFT trading at $175 per share.

If MSFT shares rise to $190, for example, we will not fully benefit from our long position initiated at $165.52 per share, because the option which we sold will expire in the money and we will be forced to sell the stock for $175 per share. This will be fully clear if we examine three potential scenarios.

Our Covered Calls Example – The Bottom Line

Scenario 1 – option expires out of the money, with MSFT trading at $170 per share

-$16,552 (initial expense from buying 100 MSFT shares) + $17,000 (closing the long position if we wish) + $605 (received options premium) = $1,053

In this covered calls case, we have made a net $1,053 from the trade, which is $605 more than if we only bought the stock, without selling call options.

Scenario 2 – option expires at the money, with MSFT trading at $175 per share

-$16,552 (initial expense from buying 100 MSFT shares) + $17,500 (closing the long position if we wish) + $605 (received options premium) = $1,553

This is our maximum possible profit, with the described covered put setup.

Scenario 3 – option expires in the money, with MSFT trading at $190 per share

-$16,552 (initial expense from buying 100 MSFT shares) + $19,000 (closing the long position if we wish) + $605 (received options premium) – $1,500 (holder exercises his options, resulting in this loss for us, as the option expired $15 below the market price for the stock) = $1,553

Scenarios 2 and 3 show us why this strategy is applied when we are only moderately bullish on the stock. If the stock price rises above the option strike price, it would have been better for us if we had not sold the call option. But in any other case, received option premium provides considerable additional income for the trader.

Three things need to be considered if you are thinking about applying this covered calls strategy.

  1. Unlike potential income which maxes out as described above, there is a theoretical possibility that you could lose your entire capital invested in a stock, if the stock plummets to zero (again, it is theoretically possible). To protect against unplanned losses, you may consider a stop-loss order on your long stock position.
  2. Broker commissions have not been included in the model. Therefore, your potential profits will be slightly lower than in this theoretical example, and your break-even point will be slightly above $159.47 for a MSFT share.
  3. Our example uses only last closing prices, both for options and stock prices. Using MarketXLS with QuoteMedia data subscription enables you to monitor bid and ask prices for both stocks and options in real-time. Start your free trial today!

Curious about covered puts?  Click here to learn more about how covered puts work and what they are.

Read More