Modern Excel can grab information from multiple files in a single folder. Just open the worksheet, refresh the query and it’s up to date!
This article is a condensed version of just one chapter in Real Time Excel – the new 2nd edition has a lot more detail and example files ready to use.
You don’t have to open a list and graft on the new records. Just drop a new data file into a folder and Excel does the rest. Or use the same method to update sales information, sport scores or any situation where there’s new data arriving.
Here’s an example with the Microsoft stock price in the 21st Century. We’ll setup the price history for past years then easily add more recent information. Then we’ll use the same query for other stocks.
Get and Transform
Start by getting some data in a form Excel can read and put them in a separate folder.
In this case, two .csv files with MSFT prices from 2000 to 2017.
You might think you need to import each of those .csv files individually but Excel has a way to grab those two and any other data files in that folder.
Put into Excel
Now open Excel and look on the Data | Get and Transform | Get Data | from File. Down the bottom of the fly-out list is ‘From Folder’.
First, fairly obviously, point to the folder where the data files are. Then see the data files available in that folder. Choose Edit to continue.
Next is the full PowerQuery editor but with a twist. The two data sources are visible.
The first column is called ‘Content’ as ‘binary’. That might seem strange because the content is really a plain text file (.csv), but don’t worry about that.
Combine
The real magic is the little double-arrow icon in the content column.
That tiny, tiny button combines the listed files and a whole lot else besides.
You can preview what Excel gets from each data file and, if necessary, change the origin type and delimiter. Once you’re happy with the settings, click OK.
When you’re back at the main PowerQuery, look at the applied steps pane. Before clicking the little ‘Combine’ button there was only a single step, now there’s six more.
On the left, there’s a lot more queries listed. The main one you want is the ‘Other query’ at the bottom. That’s the query with the combined list of data from both the .csv files.
Adding more information
That’s not the end of the story. So far, we’ve just got price data until 2017, lets add prices for the first quarter of 2018.
The new data file is in the same format as the other two. The name of the file doesn’t matter in this example.
Once the new data file is in the folder, Refresh the query.
Excel will rescan the folder, import the new data file and produce a new data table. Then refresh the PivotTable/PivotCharts to see revised results.
We’ve added two more data files, one for each month just to show that the data doesn’t have to be in specific ranges. We could add monthly, weekly or even daily data files. As long as each file has the same data format, you’ll be OK.
Now the refreshed PivotTable shows the extra months as well.
But wait, there’s more …
Real Time Excel has a whole chapter on the ‘from File’ option in Excel 2016 with many more tips and advice.
- Adding quick checks to ensure the data is complete.
- Choosing which data files to select from the folder.
- Copying the query to work for other stocks
- Converting incoming data to suit your Excel needs.
- Adding calculated columns to speed up analysis.
- Suggested free sources for historical price data.
Real Time Excel also has a detailed look at the upcoming Stock data type in Excel 2016/Office 365. For the first time, Excel can directly grab stock prices and other company info without messing about with data feeds.
