MarketXLS version 9.1, released in June 2020, added five things: a Search and Help button for finding functions, a new category of ETF data functions, historical volatility functions for stocks, faster real-time streaming with a Streaming On-Off checkbox, and an option chain filter function. This is a historical release note; the function library has grown since (the site now describes 1000+ functions), so check /formulas for current functions.
Key updates in MarketXLS 9.1
1. New function search utility
The MarketXLS 9.1 release added a Search and Help button on the far left of the MarketXLS ribbon. Instead of browsing the Excel-based function list, you search the form, which shows example usage, function names, and other details. At the time, MarketXLS had more than 600 custom functions. Click Search and Help on the MarketXLS ribbon and search for a term like "dividends," "options," or "ETFs."
2. New ETF data and functions category
MarketXLS 9.1 added functions that return the holdings within ETFs, along with ETF data such as fund family, net assets, and total assets.
3. New stock volatility functions
MarketXLS 9.1 added historical volatility functions for stocks. Volatility is calculated as the standard deviation of daily stock returns and is updated for all US and Canadian stocks and ETFs after each day's close.
4. Optimizations to real-time streaming, Refresh All, and Refresh Selected
-
The add-in now recognizes a changed symbol in a cell and starts streaming real-time data for the new ticker, without clicking the refresh buttons.
-
When a result has no value, the function returns NA instead of a message.
-
Various other optimizations make streaming data faster.
-
A new Streaming On-Off checkbox lets you quickly turn streaming data off.
5. Options filter function
The option chain filter function lets you filter an option chain by strike price and expiry date. The qm_list function can also be used for this, but the filter function is a quicker way to get to the right contracts.
For example, the following call returns all contracts from the option chain with a strike price 10% above the current price and expiry dates between 2020-06-30 and 2020-08-05:
=QM_OptionChainFilter(A1,Last(A1)*1.1, , "2020-06-30", "2020-08-05")
