Refreshing Portfolio Slicer Excel 2013 data

Before refreshing data in Excel, please make sure that:

  • You have all 4 data files in the folder from where Excel is configured to load data. If you are using scripts that are linked from this website - please make sure that you execute these scripts BEFORE EACH REFRESH in Excel.
  • Your source Excel tables do not have "empty rows" and do not have "bad data".

Steps to refresh Portfolio Slicer data are different in Excel 2010 and Excel 2013. Instructions on how to refresh data in Excel 2010 are here.

Refreshing Portfolio Slicer data in Excel 2013

With Excel 2013 Portfolio Slicer workbook you can do “All data refresh” or “Selected data refresh”.

All data refresh

With “All” data refresh you are refreshing All tables used by Portfolio Slicer at once - that is you are refreshing Date, Symbol, Transaction, Quote, Currency Exchange, etc. tables. Many tables in Portfolio Slicer have dependency between them - for example Currency Exchange, Quote and Transaction tables depend on Date table. So it makes sense to to refresh all tables together - that is why you will do “All table refresh” most often.

To do “All data refresh” in Excel 2013 you should select any cell inside any Pivot Table, do right mouse click and then choose menu option “Refresh”.

In Excel 2013 “All data refresh” can take 2-3 minutes.

 

Selective data refresh

But sometimes, after you already did “All data refresh”, you want to change data just in one table and see results after this change. This will happen most often when you are just entering transaction data into Portfolio Slicer - in such case you will want to refresh just one table - “Transactions”.

To do "Selective data refresh" in any worksheet with Portfolio Slicer reports choose menu item "Data" and then click on "Connections" button.

In "Workbook Connections" window scroll down and find table that you want to refresh, select it and then click on the "Refresh" button.

For for data refresh to complete - this should take 30-60seconds and then close "Workbook Connections" window. At this point your Portfolio Slicer workbook will be with updated data.

Video instructions - How to refresh data in Excel 2013

 

Advanced refresh options - much faster refresh

Portfolio Slicer was shipped with configuration to refresh ALL tables and files when you do refresh through “Data”->“Refresh All”->“Refresh All” menu. Every table refresh noticably increases total refresh time. On top of that Excel does data refresh not in very efficient manner:

  1. Excel loads data from external data files into PowerPivot model. This takes about 10-20sec.
  2. Excel recalculates all Pivot Table/Chart reports. This takes 30-50seconds.
  3. Excel loads data from internal Excel tables into PowerPivot model. This takes about 10-20sec.
  4. Excel again recalculates all Pivot Table/Chart reports. This takes 30-50seconds.
Initially when setting up Portfolio Slicer, you will change multiple tables, so option to refresh all tables makes sense. But after initial setup you will usually refresh Portfolio Slicer for one of these 2 reasons:
  1. You just want to update Portfolio Slicer with latest quotes and exchange rates and see financial reports based on this updated data. Actually - this will be by far most frequent reason why you want to update Portfolio Slicer workbook.
  2. You changed transaction table and now you want to see reports with these updated transactions.
Because of this it makes sense that you would configure Excel “Refresh All” option to refresh just external data files. And after you changed Transactions table (or Symbols table, etc), then you would have to refresh these tables using “Selective data refresh” method.

You can change what happens when you click on “Refresh All” buttons by changing each connection property. From menu “Data” click on “Connections” button. You will see a list of all available connections:

For each “WorksheetConnection” file you can click on “Properties…” button. In “Connection Properties” window you can uncheck checkbox “Refresh this connection on Refresh All”:
After this change your “Refresh All” menu will refresh just external files. Keep in mind that if you do not use generated dividends from external “Dividends” file, you can also disable refreshing that file.

Refreshing all tables on our demo workbook on our PC takes 136 seconds. Refreshing just External data files takes 46 seconds - significant time savings.