Refreshing Excel from .xlsx Files

A common recurring problem in excel is importing data from one or more reports that you download. This guide will not address automations for downloading the reports since there are so many options that are already widely known by programmers.

I have created an example by using stock data downloaded from: https://www.nasdaq.com/market-activity/quotes/historical

Downloading the Source Data

For this example I am going to compare the stock price performance between Microsoft and Apple as if this was a task that I would repeat daily. I start by downloading the .csv report manually. This could easily be automated in Python with Selenium or Beautiful Soup and there are probably free APIs available to more efficiently load the same data.

Next I will save the files as .xlsx with a fixed name in a good location.

The location for these should probably be a network drive so that other people in the company can refresh the same file. Make sure that the path for a network drive does not use an assigned letter since others may have assigned a different letter.

I am using .xlsx instead of .csv because it usually allows the data types to automatically be identified and it also can reduce the file size.

Linking the Data w/ MS Access

Now that we have the data we can use MS Access to use it as a table.

I use Access because the queries can easily be linked to Excel without requiring the user to install additional drivers and the query builder UI can help any users who cannot write SQL. Access does have limitations, it cannot handle more than 2GB of data and it took about 4x longer than SQLite in a test that I ran. For larger datasets you should use another DBMS but you also may run into the ~1M row limit in Excel.

To link the .xlsx files in access, follow the steps below:

Building Queries

This is not a tutorial on SQL but here are the queries that I wrote for this situation. I chose to compare the percentage change from the first Open price and to keep all the data processing in the Access queries instead of Excel. If you have a large dataset with complex queries that cause the final refresh in excel to be slow then you may want to pre-process the data by saving the final output to a table.

First I create a UNION query to combine the 2 excel files for the Apple and Microsoft datasets.

Note that in these queries you should be careful to only use the columns that you need because the report owner may add, remove, or change the names of columns. This is the main reason why people will need to follow-up for support afterwards.

Next I find the first Open price and group by the Symbol. I know that the data is already sorted, otherwise I would order by date or use the min() of the date.

Now I combine all the first opening prices with the union data and calculate the percentage change. This query displays all the data so that the user can choose to use it for any other reasons in the future without asking for help.

Linking to the Query in Excel

Use the 'Get External Data' option to import data from Access and follow the prompts.

This will create an Excel table object with the data from the query.

The built-in refresh feature can be used to update the data in this table when the source file is updated. You do not ever need to open Access again and the end user does not even need to know that the Access file exists.

Creating a refreshable Pivot Chart

Now that the data is ready, I quickly created a pivot table and chart to compare the stock price performance.

The 'Refresh All' option in Excel will update all of these objects. The sequence of the refresh applies to the Pivot tables before the Database links so you will need to refresh all twice.

If the database link takes too much time then you can right click and select Refresh on any Pivot table to just refresh that kind of object. This will require a separate refresh on each type of Pivot Table (default, classic, tables linked to pivot charts, and possibly others). If it becomes too hard to keep track of then just create a VBA solution but I try to avoid using the macro enabled Excel files.

Linking the Chart to PowerPoint

Now the chart can be linked to PowerPoint with the Paste Special menu (ctrl + alt + v) with a fixed solid background or other paste options for adjustable formatting.

You can also paste links to a cell with text from Excel to PowerPoint. This can automate the updates for month names, dates, numbers and any other text through Excel formulas and database links.

Other Adjustments

I have had issues with the color pallet in Excel not matching PowerPoint and resorted to using a linked image instead.

To quickly create consistent image files for this, run Save As in the Excel file and select 'Web Page'. This creates a folder with all the charts saved as images and the names should always be consistent when you overwrite it with updates. Another benefit to this method is that linked images load faster than linked chart objects.

In PowerPoint you should break all the links in the file before sharing with others. You also should have the Excel file with the charts open before opening the linked PowerPoint File. You may want to create a VBA solution to break the links if this results in many hundreds of linked objects.

I will post more on these solutions will be posted in the future.