Every time after the PorfolioSlicer-src.xlsx file is updated or new stock price quotes are downloaded, the Porfolio Slicer workbook needs to be updated. This articles outlines the steps required for this refresh:
- From the Portfolio Slicer folder execute _refresh-prices.bat. This will get the latest stock quotes from the Yahoo Financial website and will prepare quotes.txt file for later loading into the Portfolio Slicer workbook.
- From the Portfolio Slicer folder open the PortfolioSlicer.xlsx workbook.
- From the "PowerPivot" tab, click on the "PowerPivot Window" button. This will open a new window with table names as tabs listed at the bottom part of the screen.
- Change your current table to the most often updated table - "Quotes". This way when you do a refresh, you'll have the option to refresh just this table.
- In the "Get External Data" area click on the bottom part (area down area) of the "Refresh" button. If the PowerPivot window is small and the buttons do not fit, you might not see the "Refresh" button, but instead you will see the "Get External Data" button with arrow down. The "Refresh" button will be nested under this button.
- Bellow the "Refresh" button you will see a new menu with the "Refresh" and "Refresh All" options. Always use "Refresh All" option to reload your source transactions, date table and currency conversion rates at the same time.
- A new "Data Refresh" window will popup with information about refresh progress. When it reports that all tables are successfully loaded, click on the "Close" button to close the "Data Refresh" window. At this point data in the PowerPivot database is refreshed, but all reports still show old data.
- Close the PowerPivot for Excel window. You are not done yet!
- In the Portfolio Slicer workbook select any Pivot Table report, right click and choose the "Refresh" option.
This will now read data from PowerPivot database and will refresh all related reports. This step can take up to one minute and at that time you will see
- Save workbook so that updated data is stored and you can see it next time you open workbook.
- Refresh of the workbook done.
You can also watch the video below on how to refresh data in the Portfolio Slicer workbook