Excel Price Feed has been built to perform well with large spreadsheets and can handle spreadsheets that contain formulas for many financial instruments and data fields. You can quite easily build an S&P500 stock sheet and analyze a bunch of fundamental or live data points for each stock and not encounter any deterioration in performance.
However, if you start pushing the Add-in or Excel itself towards its “limits” or dont’ think too carefully about the format and design of your spreadsheet you may start to encounter some performance issues.
Here are some tips to help you improve the performance of your spreadsheets. Some of the tips are quite specific to using the Add-in, whilst others are more general and can be used on any type of spreadsheet:
Avoid volatile Excel functions
This issue is the probably the most common reason we see users contacting us regarding spreadsheet performance. Often a spreadsheet will use the Excel function TODAY() which is one of the Excel volatile functions. You should try to avoid using any Excel volatile functions, for a full explanation please see our blog post specifically about Excel volatile functions.
Avoid lots of single historical market data formulas
Often users wish to compare the historical performance of stocks. You can do this using the very useful single historical date formulas. I have seen many spreadsheets which use 100s of these single date formulas to return a time series. A much better alternative is to replace these formulas with a single historical data array formula. These formulas can return a time series of historical data with one formula.
If you don’t need to use all the data in the time series, or want to cross reference trading days/holidays for example, you can use the powerful Excel XLOOKUP function. In this case dedicate one tab on your sheet to time series data and reference this data from your main sheet.
Avoid single large spreadsheets
If you find your spreadsheets becoming unwieldy or suffering from performance issues due to the sheer volume of formulas and data you can look to split your single spreadsheet into multiple spreadsheets. This way you will be dealing with smaller volumes of data on each spreadsheet refresh and are less likely to breach data limits.
You can even reference cells and ranges in other spreadsheets quite easily by creating external references/links.
Convert dynamic formulas to static data
Often the data returned by the formulas on your spreadsheet does not change very often or does not change at all, especially if you are looking at fundamental or historical market data rather than live market data.
In this case you can replace your formulas with static values, using the “Paste Values” option in Excel. Select all the cells that you want to set to static, copy them to the clipboard, then “paste values” back into your spreadsheet. Using this method you can initially populate your spreadsheet using formulas, and then replace those formulas with static values/text.
This will greatly reduce the number of formulas on your spreadsheet and improve performance.
Summary
I hope these tips help you improve the performance of your spreadsheets. If you have any more tips please leave a comment below.
The Excel Price Feed Blog 