IT Budget Blog Series: Integration of Currency Exchange Rates

As part of the continuing IT Budget Blog Series, we look at how to integrate currency exchange rates.  This is especially important for two points of consideration when creating an IT Budget for international companies:

  1. Acquired Software/Hardware may not all originate where the corporate headquarters reside. This means a collection of budget line items could have more than one currency. To define a solid budget, all budget items need to be standardized to a single currency.
  2. The budget may need to be reviewed/approved by individuals worldwide. To provide a familiar denomination, you need the ability to convert all figures to a preferred currency with accuracy and ease.

This article helps discuss how to insert data from an external source (the web) into your Excel file and how often the data should be refreshed (i.e. never). Steps in this article are in reference to Excel 2016 (Version 1711) on Windows.

Our Source for this article will come from X-Rates, using $1 USD as the converter configuration – http://www.x-rates.com/table/?from=USD&amount=1

You will notice that this page includes 3 tables: a Top 10 list with most common currencies, a “complete” currency list (not sure really how complete it is), and Percent of Change. What we want to do is pull the complete currency list.

In Excel, select the ribbon tab Data, then tap From Web

EXCELData

With Basic selected, enter the URL that contains your currency rates, example URL used was mentioned earlier

WebBasic

A new windows called Navigator will appear with list of all Tables found on the page. In this example, three tables were found (i.e. it is detecting the <table> html tag in the page) and I selected the entry Table 1.

WP-0000

Before we load the data into the worksheet, tap on Edit to update some additional details. This will launch the Query Editor (if you are like me, you might be surprised by such polished interfaces! I am used to the older wizard experience found in 2010 and 2013 of Excel).

WP-0001

I launched the Query Editor as the data is not perfect. Errors include:

  • USD is the column header, not row of data
  • The USD label and value includes icons
  • There is a 3rd column that has an inverted value which will cause confusion

Here are the steps to improve the output

  1. Update name of the table (this is important as it will be used for formula references later). Look to the right side of the screen to see Query Settings and the attribute Name under properties section. I called it Currency.
  2. Remove the inverted conversion rate column on far right to remove confusion. This is done by highlighting the column (tap on header Inv. 1.00 USD), and tap Remove Column.
  3. Rename the column headers (they have extra icons in the name that will make it difficult for formula references later). Right, click on the Column header and select Rename. I changed column 1 to US Dollar and column 2 to 1.00
  4. Of course, the header column is the USD reference, thus it cannot actually be the column headers but must be a row in the data to reference in the formulas. To do this, in the ribbon Home select the drop down Use First Row as Headers and select the option Use Headers as First Row. This will move the header to the first row and create new column headers Column 1 and Column 2
    WP-0002
  5. Now rename the final column headers as discussed in Step 3 above.  I called them Currency and Value.

Now you can tap the button Close & Load to see the data loaded into a new Worksheet of your open Excel Workbook.

The final step is to stop auto-refresh. It is important for values (currencies) to remain static unless I specifically ask content to refresh as currencies continually fluctuate depending on the market, but when you work on a budget, you need constant numbers. To do this, tap on any cell in the newly created table, this will activate the Table Tools ribbon.  In Design tab, select the menu Refresh and select Connection Properties. In this window deselect all options for Refresh Control

WP-0003

Now the table can only be refreshed when you select a cell in that table and tap Refresh.

Optional Addition

You can always check the last time the data is queried by hovering on the query in the Queries & Connections panel.

WP-0004

But what if you want to display a table in the same worksheet for easy reference.

WP-0005

Here is how I did it. Unfortunately, there appears to be no easy way (without using a Macro which I do not want), so I found the following helpful article about PowerBi report which uses same concept of Power Query as Excel – https://www.excelguru.ca/blog/2016/06/08/display-last-refreshed-date-in-power-bi/

First step I did was edit my existing query (double tap on the query in the Query and Connection panel). In here I tapped on the minimized Queries panel.

WP-0006

Next, in the Home ribbon tap Advanced Editor and replace text with:

let
Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})
in
Source

This will display a single column table including current time. Next, I grouped my 2 queries by highlighting them on right (hold Shift or CTRL) and then right-click, select the option New Group.

Now when you tap Close & Load the 2 queries appear on separate worksheets. Cut the new Date and Time Query and paste in the same worksheet as your currency table.  Now when you want to refresh data, select the group in the Queries and Connections panel and select Refresh in the right-click context menu.

Note: remember to update the Refresh control settings of your new query just like you did with Currency query.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s