MultipleTickers

Import Multiple Stock Tickers Into Excel Using Power Query

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:

Folder with CSV files for different stocks.

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:

Selecting to import files into Excel from a 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:

Power Query window showing all the files in a specific folder.

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:

Multiple stock files downloaded into Excel.

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.

H2ECrypto

How to Pull Crypto Prices and Data Into Excel

In this article, I’ll show you how you can download the latest prices for cryptocurrencies, along with percent and volume changes. The data will be downloaded via an API from coinmarketcap.com. Once you’ve set up the API, it becomes a breeze to pull crypto prices and data into Excel, in just a matter of seconds.

Getting an API Key

One of the first things you’ll want to do is go onto the website https://coinmarketcap.com/api/ where you can request an API key, which you’ll need if you want to query the data. Once you have the key, you can begin pulling in values. You don’t need to worry about saving or remembering your API key because once you’re logged into the site, you’ll see an Overview section that shows you where you can copy your API key by hovering over that section. On this page, you will also see how many credits you have used today and this month versus how many are available on your plan.

Overview page on coinmarketcap.com.

Setting up the connection in Power Query

Once you’ve got your API key copied, you can go into Excel and create a Power Query connection. To do this, go under the Data tab and select the From Web button:

Creating a Power Query connection.

Then, you’ll enter the URL for the API connection, which is https://pro-api.coinmarketcap.com. On the documentation page, you’ll also see a list of possible endpoint paths. Under the basic plan, not all endpoints will be available. In this example, I’m just going to retrieve the latest market data. And for that, the URL is as follows:

https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest

I’ll put that into the Power Query URL. However, because the connection requires authentication, I need to check off the option for Advanced rather than just leave the default to Basic. In the section for HTTP request header parameters, you need to enter X-CMC_PRO_API_KEY (you’ll find this on the documentation page) and your API key. Here’s how that looks:

Entering the headers and API data for Power Query to connect to coinmarketcap.com

Then, click on OK and Power Query will go to work on creating your connection.

Formatting the data in Power Query

Once loaded into Power Query, you’ll see this:

The Power Query window after creating the connection.

If you click on the List button next to data, then you will get a series of records:

A list of the records in the Power Query source data.

On the top-left-hand corner, there is an option to convert this To Table. Click on that button, leave the default options on the next window as they are, and then click on OK. We’re still left with a long list of records. For this step, click on the icon highlighted below, at the top of the column:

Clicking on the option in Power Query to open up the records into fields.

When the next screen pops up showing you all the columns that will be expanded, click OK. Now you have something that looks a lot more useable:

Power Query table with columns expanded.

But there’s still more information that can be extracted. Scroll over to the last column, which should contain the word ‘quote’ in its name. Here there will be a list of records again. And using that button at the top of the field, this can also be expanded. It only has a USD field and once expanded, it looks like nothing has changed. Click on the header button once more, and now you’ll see fields showing volumes and price changes.

Expanded Power Query table showing more columns.

Now, you can load the data into Excel by clicking on the Close & Load button. You should now see it populate in your spreadsheet:

Power Query table loaded into Excel.

Now you can do a refresh at any point in time and your query will pull in the latest data from coinmarketcap.com.


If you liked this post on How to Pull Crypto Prices and Data Into Excel, 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.

H2Eflipdata

How to Flip a Table in Power Query

Do you want to change how your Power Query table looks? In this post, I’ll show you how you can flip your data so that you can turn a table that looks like this:

Table in Power Query that has order numbers going vertically and other fields across.

Into this:

Table in Power Query that has order numbers going across and other fields going vertically.

In the second table, it’s a bit easier to see all your fields vertically and you don’t need to scroll across to see them all. Depending on how you may want to visualize your data, you may find it useful to swap from one view to the other.

How to transform and flip data in Power Query

To transform the first field into the second field, you’ll need to take two steps in Power Query. The first is to unpivot your data. In this example, I want the order numbers to be as my headers going across, and so I will right-click on that header and select the option to Unpivot Other Columns:

Unpivoting other columns in Power Query.

That will result in the table transforming as follows:

Power Query table after being unpivoted.

This isn’t quite what I need yet, but it’s close, as it has the fields going vertically instead of horizontally. The last part is to put the order numbers going across the top. To accomplish this, I will select that column and choose the Pivot Column option to re-pivot the data again.

The Pivot Column option in Power Query.

Then, on the next screen, it’s important to select the correct values option. And, you’ll also want to select Advanced options and choose Don’t Aggregate:

Pivot column options in Power Query.

Now, I end up with a Power Query table that has been flipped and has the order numbers going across and the fields going down vertically:

A table in Power Query that has been flipped from its original layout.

How to flip the data back

Let’s suppose that you start with the above table and you want to flip it the other way (so that the attributes are going across). Here again, we’ll start with unpivoting the data back. The most important consideration is to know which field you want going across. In this case, it’s going to be the attribute field. Right-click on that and click on Unpivot Other Columns. Then, you’ll see this table:

An unpivoted Power Query table.

Now, the next step is to re-pivot the data. Select the Attribute field on the left and click Pivot Column. Again, you’ll need to select the correct value column and choose the option so that you Don’t Aggregate:

The Pivot Column settings in Power Query are displayed.

And now we’re back to having the order numbers going vertically. Although some of the fields have moved around amidst all those changes, the format is back to how it was at the beginning:

Power Query table showing order numbers going vertically and other fields going across.


If you liked this post on How to Flip a Table in 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.

stockdashboard

Creating a Stock Market Dashboard in Excel

Want to create a dashboard to track the stock market and the latest business-related news? Below, I’ll show you how you can create a stock market dashboard using Excel and Google Sheets to pull in all the data you’ll need. If you’d prefer to just download the file, you can do so here.

Step 1: Compiling the data

You can get stock prices into Excel using the STOCKHISTORY function. However, that isn’t available on older versions of Excel and it also doesn’t pull in the current day’s prices. Using Google Sheets can be more effective for this purpose. Plus, on there, I can pull in business-related news as well.

To start, I’m going to pull in values for the Dow Jones, Nasdaq, and S&P 500. I’ll also download the values of a couple of exchange-traded funds (ETFs) that track healthcare and tech stocks. To get the latest price, you can use the built-in GOOGLEFINANCE function that’s only available on Google Sheets. To get the latest value of the Dow Jones, the following formula will work:

=GOOGLEFINANCE(“.DJI”,”price”)

And to calculate the percentage change:

=GOOGLEFINANCE(“.DJI”,”changepct”)/100

For the Nasdaq, you’ll use “.IXIC” and for the S&P 500 the ticker is “.INX”

For the ETFs, since they aren’t indexes, there is no period beforehand and I reference XLK for tech and XLV for healthcare. In my Google Sheets file, I have a simple layout for the values and their changes that I will later pull into Power Query:

Stock market indicators in Google Sheets.

Next, I’ll also download the latest business-related news. Google Sheets has another unique function for this: IMPORTFEED. All you need to do is find an rss feed from a website that you want to pull information from. Not every website has an rss feed but what you can do is just do a Google search for the name of a source and ‘rss’ to see if you can find a link. There are three sources I’m going to use for this dashboard:

CNBC: https://www.cnbc.com/id/10001147/device/rss/rss.html

WSJ: https://feeds.a.dj.com/rss/RSSMarketsMain.xml

NYT: https://feeds.a.dj.com/rss/RSSMarketsMain.xml

I will pull them all in the same way, using the IMPORTFEED function. Here’s an example with the CNBC feed:

=IMPORTFEED(“https://www.cnbc.com/id/10001147/device/rss/rss.html”,”items”,true,10)

In Google Sheets, the top articles from each of those rss feeds will show up, including the title, URL, date created, and even a brief summary:

News articles pulled into Google Sheets using the IMPORTFEED function.

Now, it’s time to pull all this data into Excel.

Step 2: Loading the data into Excel using Power Query

To import data from Google Sheets into Excel, you need to first share the sheet. While in Google Sheets, go into File -> Share -> Publish to web. Then, you’ll be prompted to select what you want to share. I’ll start with the Markets tab I created and then the News tab:

Publishing data to the web from Google Sheets.

Copy this URL as you’ll need it to load the data into Power Query. While you’re back in Excel, go under the Data tab and click on the From Web button under the Get & Transform Data section. You’ll be prompted to enter a URL. This is where you’ll paste the link that you copied from Google Sheets:

Creating a query in Excel using the from web option.

On the next page, select Table 0 as where you want to extract data from. And if you want to do some cleanup (getting rid of extra columns), you can do so by clicking on the Transform Data button:

Selecting a table for Power Query to pull data from.

To remove any unneeded columns in Power Query, just right-click on a column header and click Remove:

Removing a column from Power Query.

Once you’re done, click on the button to Close & Load if you want the data to be loaded on a new sheet. If you want to control where it gets pasted, then use the drop down and select Close & Load To.

Repeat these steps for the other Google Sheets tab.

In addition, I’m also going to load data from a few other sources:

Top 100 Gainers on Yahoo Finance: https://finance.yahoo.com/gainers/?offset=0&count=100

Top 100 Losers on Yahoo Finance: https://finance.yahoo.com/losers?offset=0&count=100

Upcoming IPOs from IPOScoop: https://www.iposcoop.com/ipo-calendar/

The process for importing these links into the dashboard is the same as for Google Sheets. Go through Power Query, import from web, and paste in the URL plus make any formatting changes necessary. The next step involves putting all this data together in a dashboard.

Step 3: Creating the dashboard

In my spreadsheet, I’ve created two tabs: one that hold all my Power Query downloads (the ‘Data’ tab) and a ‘Dashboard’ tab for where all the information will be displayed.

To make the set up of the dashboard easy to manage, I’m going to change the column width to 10 for everything. To do that, press CTRL+A to select all the cells on the Dashboard tab, then right-click on any of the headers, and there you’ll be able to select column width.

First up, I’m going to get the indexes and market indicators as a starting point. To do this, all I need to do is link to the values and the percentages for the S&P 500, Dow Jones, Nasdaq, Tech, and Healthcare tickers I imported from Google Sheets. By default, I’ll set the formatting for all the cells to be green:

Market indicators imported into Excel from Google Sheets.

To make this more dynamic, I will add some conditional formatting so that if the percentage change is negative, the corresponding cells will highlight in red. For this, I can select all the cells in green above and create a conditional formatting rule the starts with where the first percentage is (in my spreadsheet, it is cell E6):

=E$6<0

This is a simple rule but by not freezing the column (E) and freezing only the row (6), it can be applied to all the cells above. I can apply a red background color so that if any of the percentages are negative, the cells will highlight accordingly:

Market indicators imported into Excel from Google Sheets with negative values showing up in red.

For the next part of the dashboard, I will copy over the news stories that were also downloaded from Google Sheets. This time, I’m going to use the HYPERLINK function so that I can not just link to the title but also create a clickable link that will allow me to open the story should I want to open it in my default browser. The function itself is simple and involves just two arguments, one for the actual URL and another for what the text should show up. Since it’s shorter, I’m going with the title. After applying some formatting and copying all three sources, this is what my dashboard looks like:

Stock dashboard showing stock market indicators and the latest business news.

For the last part of the dashboard, I’m going to pull in the tables from the other data sources (top 100 gainers, losers, and upcoming IPOs). If these are on the Data tab, you can just cut and paste them onto the Dashboard tab. And for each one of the tables, I’m going to create a chart based on the symbol and the percent change.

To do this, select the Symbol column and the % Change columns. Then under the Insert tab in Excel, open up the charts and select Treemap. If you selected too many columns or didn’t specify which ones you wanted, you might get a different look. But if you only selected those two, you should see something like this:

Treemap chart in Excel.

Since the chart includes the symbols, the legend can be deleted. Also, I’m going to change the color scheme so that it goes from dark green to light green. This change can be made by clicking the Change Colors button next to the chart:

Changing the color scheme of a treemap chart.

To add the percentage to each of the boxes, right-click on one of the ticker symbols and click Format Labels. Then, check off the box for value so that the percentages will also show up next to the symbols:

Treemap chart in Excel showing ticker symbols and percent changes.

These steps can be repeated for the other charts. However, for the losers table, since the percentage change is negative, it needs to be flipped to positive first. To do that, that query needs to be edited. If you click on Queries & Connections section under the Data tab, you’ll see a list of all your queries. Click on the one that takes you to the top losers query. Right-click edit and Power Query will open up.

Once in Power Query, select the % Change column and under the Transform column at the top, click on the Standard drop down, which will show you all the different calculations you can apply:

Power Query menu showing standard calculation operators.

Click on Multiply and then for the value in the next box, enter -1. Pressing OK will then flip all the values to negatives.

Multiplying values in Power Query.

Now, you can create the same Treemap chart for this table. For the IPOScoop download, the field I’m going to use is Est. $ Volume. This query will also need to be edited in order to use that field since it is text. Although it is a bit more complex since this field contains text and dollar signs, there’s a relatively easy way to parse out what you need.

In Power Query, select the column, and under the Add Column tab, click on the Column From Examples button (choose the option for From Selection):

Column from Examples button in Power Query.

That will create a new column:

Power Query editor after adding a new column from examples.

In Column1, I can enter the value that I want Power Query to extract. If I just enter a few values to show what I want (in this case, I only need to enter 300), Power Query fills in the rest, figuring out what I am trying to do. It’s an easy way to parse data in Power Query.

The Power Query column from examples filling in the rest of my values.

After creating the new column, I can change the format from text to currency by clicking on the ‘abc’ letters in the title:

Changing a column's format in Power Query.

Now that I have the column created, I can remove the original one and load the data back into Excel and proceed with making a Treemap for this chart using the symbol and the newly created column.

The last thing I’m going to do is create a new column to show the change in volume to determine how much more (or less) trading there was for each stock on the day compared to the average. This will compare the average three-month volume with the current day’s volume. The one complication is that some of the values contain letters:

Stock trading volumes showing letters and numbers.

To convert these values, it’s important to first parse out the letters. If a value doesn’t contain a letter, then it is in thousands. I’m going to set everything to millions. So if the value doesn’t contain a letter, it will be multiplied by 0.000001 to convert it into a fraction of a million. And if it contains a ‘B’, it will multiply by a factor of 1,000. Otherwise, the value will remain as is. Here’s how the first part of the formula will look like, which involves determining the multiplication factor:

IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))

Since the letter is always at the end of the string, just using the RIGHT function (which looks at the right-most string) will suffice. This result needs to be multiplied by the remaining value. That value can be extracted by using the SUBSTITUTE function which will replace one value with another:

SUBSTITUTE([@Volume],”B”,””)

In the above formula, the value of B will be replaced with an empty string. This is the same as simply removing the value. To ensure that any ‘M’s are also removed, I will embed this formula within another one that will substitute out those values:

SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)

I multiply this by the first part of the formula, and my numerator is as follows:

(IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))*SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)

For the denominator, I’m going to use the exact same formula, except instead of the current volume, I’m going to use the field for the three-month average:

IF(RIGHT([@[Avg Vol (3 month)]])=”B”,1000,IF(RIGHT([@[Avg Vol (3 month)]])=”M”,1,0.000001))*SUBSTITUTE(SUBSTITUTE([@[Avg Vol (3 month)]],”B”,””),”M”,””))

The last part involves putting all this together:

=(IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)/(IF(RIGHT([@[Avg Vol (3 month)]])=”B”,1000,IF(RIGHT([@[Avg Vol (3 month)]])=”M”,1,0.000001))SUBSTITUTE(SUBSTITUTE([@[Avg Vol (3 month)]],”B”,””),”M”,””)))-1

The -1 at the end is to put the change in a percentage of less than 100%.

Another step you might consider at this point to help identify these changes is to format these numbers so they are easier to read. You can use conditional formatting (color scales) to easily highlight the highs and lows. And if you want to format the percentages so that they show commas and negative percentages show up red, use the following in the custom number format:

#,##0%;[Red]#,##0%

The semi-colon before the [Red] separates out what the percentages should look like when they are positive (the part before the semi-colon) and what they should like when negative (the part that comes afterward). The [Red] text indicates the value should be in red text.

Here’s how this section looks as part of my dashboard:

Stock market dashboard showing top and bottom gainers.

And here’s a snapshot of the dashboard as a whole.

Stock market dashboard in Excel.

One thing to remember: if you want to update the queries and the dashboard, make sure you go under the Data tab and click the Refresh All button. Otherwise, your data may not be up to date.

Also, to prevent your tables from stretching out when updating the queries, select each one of them and under the Table Design tab, click the Properties button (under the External Table Data section), where you should see this:

External data properties in Excel.

Make sure the Adjust column width checkbox is unticked. This will prevent your columns from stretching out and disrupting your layout.


If you liked this post on Creating a Stock Market Dashboard in Excel, 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.

H2Efinancials

How to Import Financial Statements Using Power Query

In this post, I’ll show you how you can import a company’s financial statements into Excel using Power Query. Previously, I’ve covered how to get stock prices from both Yahoo Finance and Google Sheets. But to get financial statement information, I’m going to use a different source: wsj.com. The reason being, is it’s in an easy format to export and that makes the import process very easy for Power Query.

Downloading the data

I’m going to use Walmart’s financials for this example. And if you navigate to the following URL, you will get a summary of Walmart’s quarterly financial statements:

https://www.wsj.com/market-data/quotes/WMT/financials/quarter/income-statement

What’s convenient about this URL is that it contains both the ticker, the statement type, and indicates that the financials are quarterly. That makes it easy to alter in case you wanted to look for annual statements or a balance sheet rather than an income statement. Just changing the URL will get you to the right page. The above link is what I’m going to use for this example.

To load the data into Power Query, go to the Data tab and click on From Web:

The data tab in Excel that shows the Get & Transform data section.

Then, paste the URL in the following box:

Entering a URL in the From Web section.

After clicking OK, you can select which table to import. In this case, it’s going to be Table 0:

Selecting which table to import from a Power Query import.

Next, press the Transform Data button to make changes before it gets imported. I’ll start with removing the column at the very end, showing the trend, as it doesn’t contain any information. To remove it, right-click on the header and click Remove:

Removing a column from Power Query.

I’m also going to remove the Changed Type step, which automatically changes the data types. To get rid of the step, click on the X next to the step:

Removing a step from Power Query.

This is important because since the header names change based on the quarter, it isn’t going to be helpful to have this step since it looks for hardcoded values. An optional step you could take is to Demote Headers so that the header names are generic and not tied to a specific quarter. However, this isn’t necessary if you remove the Changed Type step. For more information on changing header names, refer to this post.

Once you’re done making changes, click on Close & Load in the top-left corner, and then your data will load into a sheet.

Close & Load button in Power Query.

The download will work just fine right now. However, let’s also make the file a bit more versatile in case you want to quickly change the ticker symbol.

Setting up the variables

First up, I’ll create a named range for the ticker symbol, called ‘Ticker’ :

Power Query table with a variable for a company's stock ticker off to the right.

I’ll now go back into the query editor to account for this named range. To edit a query, go into the Data tab, click on Queries and Connections, and then off to the right you should see your queries. Right-click edit on the one you want to adjust:

Selecting the option to edit an existing query in Excel.

Then, click on the Advanced Editor button near the top of the Power Query window:

The Advanced Editor button located on the Power Query Home tab.

I’m going to add the Ticker variable under the let section as follows:

Ticker = Excel.CurrentWorkbook(){[Name=”Ticker”]}[Content]{0}[Column1],

Note that Power Query is case-sensitive and you will get an error if what you’ve entered doesn’t match exactly what you’ve set as your named range. Also, make sure to add a comma at the end.

I will also need to adjust the Source variable so that it uses the Ticker variable:

Source = Web.Page(Web.Contents(“https://www.wsj.com/market-data/quotes/”&Ticker&”/financials/quarter/income-statement”)),

The key thing here is to break up the part of the URL that mentions WMT and replace it with the named range. Here’s what the code looks like within the Advanced Editor:

Power Query code in the Advanced Editor.

Now, you can Close & Load back into the worksheet. To test the named range, what you can do is replace the ticker value from WMT to AMZN, and if it works correctly, it should load Amazon’s income statement instead. After changing the ticker symbol, remember to press the Refresh All button under the Data tab:

The Refresh All button in the Data tab.

If it works, you should see a whole new set of data populate on your spreadsheet:

Amazon's income statement loaded into an Excel spreadsheet using Power Query.

If you liked this post on How to Import Financial Statements 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.

H2Efxrates

How to Pull Foreign Exchange Rates Into Excel Using Power Query

In a previous post, I showed how to get stock prices using Power Query. This time around, I’m going to show you how we can do the same for foreign exchange rates, pulling that data into Excel. For this example, I am going to use the currency conversion site xe.com.

Creating the connection

The first thing that we need to do when creating a Power Query connection is determining which web page the data will come from. On the xe.com website, there is a currency table for each day. If I wanted to pull the USD foreign exchange rates into Excel as of August 22, the URL I would use is as follows:

https://www.xe.com/currencytables/?from=USD&date=2021-08-22

The link is convenient because I can easily alter the currency and the date. I’ll go over how to do that later but first, let’s create the connection. To do that, go in the Data tab on the Ribbon and click on From Web button under the Get & Transform Data section:

Get and Transform data section of the Excel ribbon.

Then, on the next screen, I’ll paste the link and click OK:

Entering in the URL for Power Query to pull data from the web.

Once it has loaded, there will be multiple tables to choose from. Table 0 is the one that has the exchange rates:

Selecting the correct table from the Power Query download.

Instead of clicking to load the data, click on Transform Data to make any adjustments to it before it loads into Excel.

Adjusting the query

Once the query is loaded, you’ll see the following window:

Power Query window with foreign exchange rates.

You probably don’t need or want to see all these rows in Excel. So what you can do before loading it is to clean the data up a bit. Let’s say I only want to pull the US exchange rates in Excel for EUR, GBP, CAD, and AUD. To do that, I’ll click on the Currency header and filter for just those options. You can filter the same way you would an Excel table. And once you’re done, you should see the table get a whole lot smaller:

Power Query table after filtering currency values.

To cut down the table even more, I can remove the Name field since. To remove any column, simply right-click on it and click Remove:

Removing a column from Power Query.

Clicking on the Load & Close button will now populate this into my Excel sheet:

Foreign currency rates loaded into Excel.

Now, let’s alter the URL so that it can be adjusted dynamically for both the currency and date.

Using variables in the Power Query link

An advantage of using a link that has the currency and the date in it is that it is easy to change. I’m going to start by creating a couple of variables. The first is for the currency, and the second is the date. I’ve set up named ranges of ‘Currency’ and ‘Date’ the following fields:

Named ranges in Excel for Currency and Date.

When entering the date, I’m entering an apostrophe (‘) first so that formatting isn’t an issue and Excel reads the value as text.

What I’m going to do next is go into Power Query and create these variables. To do so, go back into the Data tab and click on Queries & Connections. Off to the right, you should see your query. Right-click on it and select Edit:

Editing a query in Excel.

This will open Power Query back up. I’m going to click on Advanced Editor button on the Ribbon, under the Query section:

The query options in the Power Query Home tab.

At the top of the code, I’m going to insert two lines. One for each variable:

Currency = Excel.CurrentWorkbook(){[Name=”currency”]}[Content]{0}[Column1]

Date = Excel.CurrentWorkbook(){[Name=”date”]}[Content]{0}[Column1]

I will also need to adjust the Source, so that instead of this:

Source = Web.Page(Web.Contents(“https://www.xe.com/currencytables/?from=USD&date=2021-08-22”))

It becomes this:

Source = Web.Page(Web.Contents(“https://www.xe.com/currencytables/?from=”&currency&”&date=”&date))

The variables are ready to go. But before using these them, it’s important to remove the Changed Type step from Power Query:

Removing the changed type step from Power Query.

This step looks for exact column names which can cause an error if your names change when downloading data.

Now with that done, you can change the values and your table will change. Here’s what it looks like if I change the currency to AUD and set the date to July 31:

Excel table showing different foreign exchange rates.

The one problem, however, is that now I have AUD and don’t have USD. What I can do is create a separate table of all the currencies I want to pull in:

List of currencies in Excel.

I can load this table into Power Query by going back into the Data tab and this time clicking on the From Sheet option. I’ll rename the FXtable and it shows below the other table:

FXtable created in Power Query.

Now, if I only want to see the values from FXtable, what I will need to do is merge the queries.

Merging queries in Power Query

Switch over the Table 0 query and get rid of the Filtered Rows step. Then, on the Home tab, there is an option to Merge Queries in the Combine section that you’ll want to click:

The combine section in Power Query.

On the next screen, I’ll select the FXtable from the drop down and select the currency fields from each table:

Merging tables in Power Query.

Leave the default of Left Outer selected and then click OK:

Power Query after applying merge tables.

Next, click on the button on the FXtable to expand the table:

Power Query button to expand table.

Click OK on the next section to expand the only field from that table:

Selecting which fields to expand in Power Query.

Which will result in this:

Power Query after expanding the table.

Then, just filter the FXtable.Currencies field so that null values don’t show up, and you’re left with just the currencies that were present on that table:

Filtering out the null values in the FXtable.Currencies column.

Now that the table has served its purpose, I can remove the FXtable.Currencies column and I’m back to what I had before:

Power Query after removing the FXtable.Currencies column.

Now, I can modify the base currency, the date, plus the currencies that I want to show up. Suppose I just want to see the EUR-GBP currency rates for Jan 1, I could enter the following values:

Spreadsheet showing named ranges and the foreign exchange rates.

All you need to do is refresh the data from the Data tab and all the queries will update:

The Refresh All button in Power Query.

And the data matches what comes from the xe.com site:

The Foreign Exchange rates showing in Power Query.

Are you looking for a way to pull historical data for a currency pair? You can use Yahoo Finance to do that, and the steps for doing that query is similar to how you would pull stock quotes from there (refer to the link at the top of this post).


If you liked this post on How to Pull Foreign Exchange Rates 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.

H2Eheadernames

Dealing With Tables With Changing Headers in Power Query

One of the more common errors you’ll run into when using Power Query is when your headers change names. If that happens, and you have a step in Power Query that alters the headers or relies on them in any way, you’ll end up with an error saying a header wasn’t found. This can happen if you are querying a table whose headers change or if you simply change the name of one of them yourself. Below, I’ll show you an effective strategy for dealing with tables with changing headers in Power Query so that even if the header names change, you can avoid running into these errors.

Changing headers in Power Query

Let’s start with the basics of how you would normally create headers in Power Query. For this example, I’m going to use data from the City of New York Expenses, which you can download from here. This is what the data set looks like:

City of New York Expenses in Excel format.

To launch this data into Power Query, go to the Data tab and click on From Sheet. Once the range is selected, click on OK. Once in Power Query, the data looks as follows:

Expense data that is populated in Power Query.

To change any header name in Power Query, all you need to do is double-click on the header. In this example, I’m going to change the first header so that rather than Publication Date, it will just say Date. Then, I’ll go in and click Close & Load to get out of Power Query.

The problem arises if the header in the table were to change. If I go back into the Expense_Actuals sheet (which Excel automatically created for me when I set up the table in Power Query), it shows these headers:

Header names in the expense table.

And if I change the first header so that it looks like this:

Table headers after changing the name of the first header.

I’m going to have a problem, because Power Query is going to be looking for Publication Date rather than just Date. Now, if I go under the Data tab and click Refresh All — which will update the query — I get the following error:

Power Query error showing that the column wasn't found.

This is the error that shows up when Power Query can’t find the header. If I modified the header name for Fiscal Year or any other column, then I wouldn’t get this error. The reason is the step of changing Publication Date to Date is hardcoded into Power Query and if it doesn’t find that header, it will give me the above error message.

The key to dealing with tables with changing headers in power query is to demote them, which I’ll cover next.

Promoting and demoting headers in Power Query

By default, Power Query will use the first row of your table as its headers. In my situation, this is a problem because if the header in the table is changing, I could run into errors. To eliminate this, I’m going to demote the headers. To start, I’ll go back to edit the query. To do this, you can go under the Queries & Connections table, right-click on the query and click on Edit:

Selecting the option to edit the query.

Then, select the first Power Query step (which will likely just be ‘Source’), and you should again see the table again:

Table in Power Query.

Then, under the Transform tab, in the Table section, click on the option to Use Headers as First Row:

Select the use headers as first row option in Power Query to demote headers.

This has the effect of demoting the headers so that they are now rows. And upon doing so, the header names become just Column1, Column2, etc:

Power query table after demoting the headers.

The option above it to Use First Row as Headers is the exact opposite — it would promote the first row and make that the header for all the columns; it would undo the above step.

However, now with the plain column names, what you can do is go through and re-name each of the headers however you want, which now looks as follows:

Power Query table after changing the header names.

The one issue that remains here is that now we have the old header names in the first row. To get rid of them, go into the Home tab and click on Remove Rows and select the option to Remove Top Rows

Removing the top rows from Power Query.

And then for the number of rows to remove, just enter 1 and click OK:

Removing the first row from the Power Query table.

And then, the updated Power Query table looks as follows:

You can remove any other steps that were previously in Power Query to avoid it looking for the old header names.

One thing to also remember is the Changed Type step which Power Query automatically generates and looks to convert each header into the correct data type. As you can see from the formula for that step, it will look for the hardcoded header names:

The changed type in Power Query also looks for exact header names and can cause issues as well if you haven't removed this step.

You can either modify the header names within the formula, or you can just remove the step entirely. But this too can cause issues if it is looking for a hardcoded header name. Once you’ve made the last step of removing or changing the Changed Type step, you should be good to go.

Now, when you go and refresh your query, even if you have modified the header names, your data will refresh correctly and put in the header names you have specified in Power Query.


If you liked this post on dealing with tables with changing headers in 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.

H2EConditional

How to Make a Conditional If Statement in Power Query

In Excel, IF statements give you way to handle multiple scenarios. You can determine which result to return based on another value or input that a user makes. A common example is where a cell contains no value. You can create a formula to say if the value is blank, you return a result that is blank. And if it isn’t blank, you can perform a calculation. IF statements work similarly in Power Query although you can’t enter them in cells. Below, I’ll show you how you can create a conditional IF statement in Power Query and how you can use it in your data set.

In this example, I’m going to use data from the data.gov website on the Tuition Assistance Program. You can download the CSV data from here if you want to follow along.

Getting the data into Power Query

Once you have downloaded the data, the first step is to pull it into Power Query. For that step, just click anywhere on the data set and under the Data tab, click on the option to get data From Sheet:

The Get & Transform data tab in Excel.

The data is fine in the shape that it is right away so there is no need to make any changes when loading it into Power Query.

Creating a Conditional Column in Power Query

Suppose we wanted to just differentiate the data between whether the funding is related to the private sector or the public. You could do a pivot table but if you want to just have a column to pull in those amounts separately, you can create a conditional column. A conditional column works like an IF statement, only it is easier to set up.

One thing to remember with Power Query is if you want to just alter the current column, you want to stay on the Transform tab. But if you want to create a brand new column — which is what I’ll be doing in this example — you want to go onto the Add Column tab at the top:

The transform and add column sections in Power Query.

Once you are on the Add Column section, you will see an option for a Conditional Column right below it:

Add column options in Power Query.

Click on that button, and then you will see the following window:

Add conditional column window in Power Query.

For the column name at the top, I will call it Private Funding, since that is what I want to calculate. And the criteria is simple: I’m going to set it so that if the Sector Type column is equal to PRIVATE (this is case-sensitive in Power Query), then the output will be the TAP Recipient Dollars column. Otherwise, I want the value to be zero. Here is what that looks like:

Add conditional column window in Power Query with data filled in.

You’ll notice that on the output, value, and else fields, there is a down arrow. Clicking on this will allow you to switch between a column or a value. You can specify if you want to enter a value or reference a column. In this case, I want to reference an entire column if the criteria is met. And if it isn’t, I want to set it to a value — zero. For the operator, you also don’t need to look for an exact match, that too can give you various options:

Different operators in Power Query.

Once that is set up, I have a column called Private Funding in Power Query that is equal to the TAP Recipient Dollars if it is Private funding only. Otherwise, it is set to 0:

Private Funding conditional column set up in Power Query.

Now, I can repeat these steps for Public Funding and will now have a value in either private or public funding:

Additional columns created for private and public funding.

You may think this is a bit redundant but it saves having to create a pivot table if I wanted to do a summary (or a SUMIF function). One of the great things about Power Query is when I no longer need a column, I can just delete it. If I right-click on the original Sector Type column, there is an option to Remove from the shortcut menu:

Removing a column in Power Query.

This doesn’t impact my table because Power Query saves the steps I take and each time repeats the same order. This way it is safe to remove the unnecessary tab and avoid having redundant data that isn’t needed anymore.

Using the conditional column option is easy but if you want something more versatile to possibly include other Power Query functions, you can also use the Custom Column button, which I’ll cover next.

Creating an IF Statement Using a Custom Column

The option to create a Custom Column is also under the Add Column section:

Custom column option in Power Query under the Add Column section.

In this example, I will create a conditional column to look at if the TAP Level of Study column indicates at least a 4-year degree. By looking at the values there, we can see that the years are indicated in the first number:

Column in Power Query showing level of study.

If this was in a spreadsheet, I could just use the LEFT function to extract the first number. But in Power Query, I’m going to do it a little differently. Instead of the LEFT function, I am going to use the Text.Start function (these are also case-sensitive), which works the same way:

Text.Start([TAP Level of Study],1)

In this formula, I’m selecting the field, TAP Level of Study, and extracting just the first character from that. However, I still need to convert this into a number if I want to evaluate it as one. Next, I need to enclose this within the Number.FromText function. My formula looks like this:

Number.FromText(Text.Start([TAP Level of Study],1))

The next step is to evaluate it to see if the value returned is greater than or equal to 4:

Number.FromText(Text.Start([TAP Level of Study],1)) >= 4

If I am content with just getting back a series of TRUE or FALSE values, then I can stop here. But if I want to customize the values to say ‘YES’ or ‘NO’ then I will need to add to this formula by adding an ‘if’ statement at the beginning. I will also need to use the ‘then’ and ‘else’ keywords to tell Power Query what I want the results to be:

if Number.FromText(Text.Start([TAP Level of Study],1)) >= 4 then “Yes” else “No”

This is how it looks in the Power Query Custom Column window:

Creating a custom column formula in Power Query.

As you can see, going through the Custom Column approach will give you more flexibility as to what you can do with your conditional statements. While the Conditional Column is easy to use, it isn’t as flexible as you might need it to be. Now, when I click OK to create this column, I know have values that show either ‘Yes’ or ‘No’:

Column in Power Query that was created with Custom Column.

If you are looking for other Power Query functions, you can check out this page.


If you liked this post on How to Make a Conditional If Statement in 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.

H2Eunpivot

How to Unpivot Data in Excel

Using pivot tables to summarize data can be a great way to display information quickly and total everything up. However, in some cases, data that you download is already in what you might call a pivot table format where it is summarized and you want to put it in more of a tabular format. In this post, I’ll show you how to unpivot data in Excel where you can turn a table like this:

Data in a summarized, table format.

into this:

Data that has been unpivoted.

Unpivot using Power Query

Rather than copying and pasting data into a tabular format and doing the process manually, you can just use Power Query to do it for you, all in a matter of seconds. First thing’s first, you need to get your summarized data into Power Query. To do that, click on one of the cells in the table and on the Data tab, click on the From Sheet button in the Get & Transform Data section:

Selecting the From Sheet button on the Get & Transform Data section.

Then, click OK on the default range and then the next screen will be Power Query:

Table showing in Power Query.

The key to making the unpivot work correctly is to determine which column(s) you don’t want to unpivot. In this case, it is only the Year field as I want to have the years listed out. With the Year column selected, I right-click on the header and select Unpivot Other Columns:

Select Unpivot Other Columns from the menu.

After clicking on that, the data is unpivoted and now it is in tabular format:

All that is left now is to press the Close & Load button in Power Query, which will then populate the data back into Excel:

You can repeat these steps for other, similar summarizes should you need to unpivot data.


If you liked this post on How to Unpivot Data in Excel, 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.

H2EParsePowerQuery

How to Parse Data in Excel Using Power Query

In an old post, I went over how to parse data using various different functions. This time around, I’m going to show you how much easier it is do that in Power Query. If you’re not comfortable using LEN or MID functions, then this will make your life a whole lot easier. And to keep things simple, I’m going to use the same data set as I did in the previous post, which you can download from here.

Setting up the query

The first step involves copying the data from the webpage and then just pasting it into cell A1. With no adjustments, my data just contains the raw data:

Raw data download.

The one thing I’m going to do is remove the blank rows just so that Excel recognizes the full data set without having to adjust it. I can remove the blank rows in Power Query, but I’m going to do it at this stage so I don’t need to worry about finding what row number I need the range to go down to. To remove blanks, I will select column A, press F5, special, blanks, and then right-click delete on one of the cells. Now that there are no gaps in my data, I can set up the query.

To do that, I’ll go into the Data tab, and in the Get & Transform Data section, click on the From Sheet button.

Get & Transform section of the Data tab.

Excel should now autodetect the entire range. Click OK and the query will be created:

Data exported into Power Query.

Right now, it looks the same as what it was before, except it’s in the Power Query window. Next, I’ll actually start making the transformations.

Parsing the data using delimiters

Just like in the older post, I am going to set up fields for Country, City, and Population. But this time, you won’t have to fumble around and worry about setting up complex formulas. In the Power Query Editor, I’ll select the Add Column tab. And in there, I’m going to select the Extract drop-down selection and choose Text Before Delimiter:

Extract menu in Power Query.

I’m going to use the colon (:) as the delimiter and then click OK

Setting up the text before delimiter in Power Query.

That nicely parses out the countries:

Applying the text before delimiter for the Country field.

I can double-click on the header where it says Text Before Delimiter and change it say ‘Country’

Next, let’s parse out the City field. I need to make sure that Column1 remains selected. This time, I’m going to select Extract under the Add Column tab and then select Text Between Delimiters. I’m going to set my start delimiter as a colon. And the end delimiter will be the opening bracket:

Setting up the text between delimiters in Power Query.

And after re-naming the field to City, this is why my Power Query Editor looks like:

Power Query Editor after setting up the Country and City fields.

The last column to parse out is the Population. For this, I’m going to follow a similar step as above except I’m going to extract the text within the brackets. But in some instances, there is data within brackets that doesn’t relate to the population. But one consistency is that the population always comes at the end. So in this case, I’m going to use the Advanced options and specify that I want to start searching from the end of the string. I have left the other options the same:

The Advanced options in the Text Between Delimiters section.

Now, my fields look pretty good:

Country, City, and Population fields set up in the Power Query Editor.

The one thing I still need to do is remove the headers for the different letters. Since there is nothing in brackets, I can filter for any blank value in the City field. To do, this, I will click on the drop-down arrow for that field and select the option to Remove Empty:

Removing empty values from the City field.

Now, the data looks good and ready to import back into the worksheet:

City field after removing the blank values.

I technically don’t need that first column anymore. It’s done its job and one of the great things about Power Query is I can delete it, and it won’t impact everything else I’ve done. I’m going to right-click and delete that column so that all I’m left with are the fields I actually need:

Power Query Editor after removing the first column.

All that’s left now is to load the data into the spreadsheet. To do this, click on the Close & Load button in the top-left corner. It will now put that into a new tab by default:

Power Query data loaded back into an Excel sheet.

And just like that, you’ve parsed the data without having to go a painstaking effort of figuring out the correct formulas. But as easy as this was, there is an even easier way of parsing the data out (most of the time).

Parsing the data using examples

I’m going to re-do the previous step, this time taking a different approach, without the use of delimiters. This time, I’m going to the Add Column tab and select the Column From Examples button. This will generate another column on the right-hand-side:

Using Column From Examples in Power Query.

What I’m going to do in Column2 is give Power Query some examples of what I want this field to contain. Since it is the Country field, I’m going to start by typing out a country name. Even after just entering the first one, Power Query has figured out the pattern and does the rest for me:

Column From Example after entering in one value for the Country field.

You’ll notice at the top it has the Text Before Delimiter which is what I used when I did this manually. The less complicated the data, the quicker and easier it will be for Power Query to predict what I’m trying to do.

I’ll repeat the step for the City field. This time when I enter just one value, it hasn’t figured out the rest of the values:

Column From Example after entering in one value.

Instead of La Paz at the bottom of the above screenshot, it only pulls ‘La’ and so what I will do is correct that entry manually. Upon doing that it updates the calculations, but they still aren’t quite right. For Bosnia and Herzegovina, it is including part of the country name:

Column From Example after entering in two values.

I will manually update that value to just enter Sarajevo, and once I do that it now looks correct:

Column From Example after entering in three values.

And if I look at the formula that it has generate, it now is the same as what I did manually with selecting the delimiters:

Column From Examples formula.

The last column, Population, was the most challenging to set up because I needed to use the Advanced settings. Let’s see how well Power Query is able to extract this one using examples. Again, I’ll start with entering in the first value:

Column From Example after entering in one value for the population.

It doesn’t look too bad except for La Paz, it pulls in ‘seat of government’ which is in brackets, as opposed to the population. I’ll manually correct this one, and upon doing so this is what my column looks like:

Column From Example after entering in two values for the population.

Now the problem is the n/a values aren’t picking up correctly. Once I correct them, the column looks to be correct, except for Delhi:

Column From Example after entering in three values for the population.

After making a few more adjustments, the column looks to be correct:

Column From Example after entering in several values for the population.

One of the challenges with doing it this way is if a field isn’t easy to predict for Power Query, it may take some manual entry before it is able to get it just right. And even then, you may not be certain that you’ve accounted for all the possible variations. While this method can make it really easy for simple data parsing, for more advanced ones you will likely want to familiarize yourself with how to use the different Extract options.


If you liked this post on How to Parse Data in 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.