Power Query can allow you to easily import data from another spreadsheet. But did you know that you can load multiple files from a folder at once? All you need to do is load the files you want to import into a folder, and Power Query can do the rest. In this article, I’ll show you how you can do this with stock prices and how you can import multiple ticker files from Yahoo Finance into Power Query at once.
Put all the files into a single folder
Whatever type of files you want to import, the key thing is that their format is consistent. This is because Power Query will follow a similar process when importing them. If, for example, you always remove certain columns from a file, then you want to make sure that every file you import has those columns. If there’s a discrepancy, then Power Query may struggle to load the files properly.
In this example, I’m going to use CSV files from Yahoo Finance. Let’s say I want to download data for multiple stock tickers. If I go to Apple’s stock ticker page, there’s a link to download the latest stock prices. In a previous post, I went over how to download stock prices for a single ticker. This time around, I’ll show you how you can do it for as many as you want. If I want to download multiple tickers, I’ll start by downloading all the different CSV files for them and putting them into just a single folder:
Here I’ve got multiple tickers downloaded, including Apple’s. This is now the folder I will reference when extracting the data from Power Query.
Importing the files Into Power Query
In Excel, the next step is to simply download the data. Under the Data tab, click on the Get Data button and select the option for From Folder:
Then, navigate to the folder where your files are stored and click on Open. Now the Power Query window will load and you should see something like this:
Here I see all the files from my folder. There are three different options I can take at this point:
- Transform Data. Clicking on this option will allow me to transform the table above.
- Load. If I don’t want to make any transformations and just load the table above, this is what I’ll select. But like the above option, this will not combine the data, so this is not what I want.
- Combine. This is the option that I will choose as it will combine all these files together. From here, you’ll have the option to Combine and Transform or to just Transform and Load (e.g. if you don’t need to make any adjustments).
On the next screen, you can click on OK and the combined data will be loaded. To make the process as seamless as possible, you’ll want to ensure that your files follow the same format. Otherwise, it can be more difficult to get the desired results.
After clicking on OK, now the data loads, and all my stock data from Yahoo Finance is downloaded, with all the different tickers:
Now, you can add more downloads from Yahoo Finance for different tickers, put them in the same folder, and then just refresh the query. Your spreadsheet will now automatically update based on the CSV files within the folder.
If you liked this post on How to Import Multiple Stock Tickers Into Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Add a Comment
You must be logged in to post a comment