Daily exchange rates
- Jan. 04, 2012
 | Example files with this article: |
Introduction
TM1 models use multi-currency aspects rather often. Usually, a monthly exchange rate is enough detail for the model. Yet, I can imagine there would be business cases where a daily exchange rate is needed. For instance, for converting EUR values to USD or YEN. In this article, we will focus on daily exchange rates, but the principles for monthly rates will be the same.
At any rate, either the company provides the rates, either services like the world wide web are used to get the rates. In the first case, a relatively simple Turbo Integrator process could load the rates each night when they are available. Provided such a system delivers access to database tables, flat files, or other.
Get the rates ourselves
What are the options in case we need to grab such rates ourselves? (The method in this article is generic, I only apply it to exchange rates for the illustration purposes) We could have someone in the organisation who visits a particular website day after day, and tracks the rates. After that, input the rates in TM1 or use the DBSW function in Excel. Or you could have custom VBA coding that automates the same process by the click on a button. For instance, an URL like http://www.exchange-rates.org/Rate/USD/EUR/7-12-2011 can be chopped into pieces and then we insert the currencies and the formatted date. Even programming the TM1 API is a possibility if you have that knowledge.
Excel web query
My choice in this article is a semi-automated approach of a web query in Excel. Using Excel 2007, here are the steps for loading daily historical USD-EUR rates into a TM1 cube (see later):
- Choose Data > From Web
- Navigate to
http://www.exchange-rates.org/history/EUR/USD/T and click "Import".
Change the currencies in the URL if you need other ones. In the top right corner you could also set options
(for example to have the web query output HMTL formatted data instead of plain text). I checked that
I would like to disable the date recognition. See below for more information.
- Then select the large table in the middle of the page. Again, click "Import".
- Choose "Properties…" and fill them out the way I suggest below
- The first 20 rows in the output will look like this:
Convert the output
Then, we only need to convert the returned data, and load in our TM1 cube. The TM1 cube is comprised of 6 dimensions:
- Currency_From (a list of currencies we track)
- Currency_To (a duplicate of the list of currencies above)
- Days (01 to 31)
- Months (01 to 12)
- Years (2005 to 2015)
- Currency_Rates_Measure (several rates to leave the dimension generic)
In the worksheet screenshot above, I purposefully left columns A and C wide: it means that the dates and numbers are TEXT instead of numbers. They are left aligned, again proof of that. So we need to convert them to days, months, years and a numeric exchange rate value. Some other points to note:
- Connect to the specified server name using TM1 Perspectives. Fill out cell O9 correctly;
- Look at the formulas for the different columns. They are self-explanatory I guess;
- I format days and months as "00" since my element names in TM1 are like that. An alias is an option too;
- I used a password cell to avoid accidental uploads to TM1. The password is pw.
The final result
The result in TM1 after refreshing the worksheet with correct password, is:
Reusing the file
You can now use the same Excel file again, since the web query definition (the connection and the parameters) is baked into the file. Later I might whip up some VBA-code to automate Internet Explorer or web queries, but this method will do just fine. What is interesting, however, is that we can have parameters in the web query itself. This could be a topic for a later blog article.
