The latest release of Excel is focused on performance improvements. The main areas that have been addressed are RealTimeData functions, faster opening of workbooks with many user defined functions and faster aggregation.
RealTimeData functions (RTD) are commonly used to update a spreadsheet from a real-time data source. For example, if you have a stock portfolio you can add RTD functions to your sheet to provide real-time prices: as the stock moves, the price in the Excel cell is updated automatically:
Microsoft has removed bottlenecks in the underlying memory and data structures and also made it “thread safe”. The result is a significant performance improvement on sheets with lots of RTD functions.
I have noticed with one of my testing spreadsheets, with lots of RTD functions, that Excel now consumes less CPU and is generally more responsive.
User Defined Functions (UDFs) are custom functions that can be created by users or provided by an Add-in to add additional functionality to Excel. These functions operate the same as regular functions/formulas.
Opening workbooks with many UDFs was previously very slow as Excel looked up each UDF on the sheet. Now, Excel includes a UDF cache to make this process much faster.
Aggregation functions like SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS are some of the most commonly used Excel functions. With the latest release of Excel the performance of these functions is drastically improved.
We are pleased to announce today our partnership with data provider EODHistoricalData.com which provides comprehensive historical and fundamental data from 60+ exchanges around the world.
This partnership significantly increases the data coverage of Excel Price Feed especially fundamental data as we can now offer up to 20 years of company financial history for many stocks.
For example, here is an extract of the Financial Analysis tab for Microsoft stock from our sample spreadsheet:
Excel Price Feed is unique in the way it enables financial data to be embedded in a spreadsheet using simple Excel formulas, no more VBA or complicated web requests.
For example, to add the current market capitalization of Apple stock to a cell, the following formula is used:
Then, each time the spreadsheet is refreshed the latest data is retrieved.
Company fundamental data that is now easily available in Excel via the Add-in includes current market capitalization, EBITDA, PE Ratio and PEG Ratio:
Our launch integration includes historical price data (daily, weekly and monthly) as well as stock fundamental data and financial analysis data. Over the coming months we will be adding access to more datasets such as technical indicators, economic and calendar data. We will also expand our instrument coverage to include more bonds and options.
Here is a chart of long client sentiment (the orange line) versus market daily close (the blue line) for the S&P500 Index over the past 4 months:
There does appear to be a correlation, as the market moves up client long positions moves down – clients are moving from long to short positions as the market goes up.
Then at the peak (early September) client positions were long just before the market started dropping.
As the market moved down (i.e. for the past month) clients stayed long.
This example shows that client sentiment data can be used to provide trading signals, especially at “turning points” in sentiment. Obviously this would need to be tested against different markets but it is definitely a useful tool when looking for trading opportunities.
There are 8 built-in volatile Excel formulas, and it is worth being aware that using these in your spreadsheet could have unintended consequences:
INFO (depending on its arguments)
CELL (depending on its arguments)
SUMIF (depending on its arguments)
The solution was simple, replace the TODAY() function with a string value of the current date. This could be either hardcoded into the formula or better still entered into a cell and referenced from the formula.
So, if you every notice your spreadsheet starting to slow-down or start doing unnecessary calculations then look out for volatile functions.