Currency Conversion in PowerPivot (FX, Oanda)

[unclesomebody]

I have a table containing sales, which has 4 columns; date, item name, currency, price. We sell in multiple currencies and the price in this table is the currency of the sale. So, in this table we have EUR, GBP, USD, SKK, NOK, DKK, etc. I would like to create a fifth column in my sales table which displays the price in local currency (in my case; GBP).

I currently have 2 issues

1. How do I get a table in powerpivot which AUTOMATICALLY populates with FX data from Oanda?
2. How do I use the fx rate for a particular day and use it to do the conversion in the sales table?

I have managed to find a way to get excel to talk to Oanda thanks to a macro I found at this page: Download Historical Forex Data into Excel. That allows me to get historical FX data for a single currency pair and I can then create a table with many currency pairs (all baselined from GBP). This table has date, USD, EUR, NOK, DKK, etc as it's columns. I'd like to find a way to get this clever bit of code into powerpivot but it currently creates a URL, downloads a table, converts it, and displays it in a sheet.

The next issue (which is the major one for now) is that I can't figure out how to create the fifth column in my sales table that looks up the date and the local currency, then finds the corresponding rate in the FX table, then multiplies that by the price. This would be an insanely easy task in excel but my DAX skills are sorely lacking. I did find this web page which has a solution of sorts, but this doesn't use a different rate for every day: Prodata Blog | Many to Many Currency Conversion with PowerPivot

If anyone could help me or nudge me in the right direction I'd appreciate it. And if anyone has a solution to getting Oanda rates into powerpivot then that would be brilliant too!

 

[unclesomebody]

I have a table containing sales, which has 4 columns; date, item name, currency, price. We sell in multiple currencies and the price in this table is the currency of the sale. So, in this table we have EUR, GBP, USD, SKK, NOK, DKK, etc. I would like to create a fifth column in my sales table which displays the price in local currency (in my case; GBP).

I currently have 2 issues

1. How do I get a table in powerpivot which AUTOMATICALLY populates with FX data from Oanda?
2. How do I use the fx rate for a particular day and use it to do the conversion in the sales table?

I have managed to find a way to get excel to talk to Oanda thanks to a macro I found at this page: Download Historical Forex Data into Excel. That allows me to get historical FX data for a single currency pair and I can then create a table with many currency pairs (all baselined from GBP). This table has date, USD, EUR, NOK, DKK, etc as it's columns. I'd like to find a way to get this clever bit of code into powerpivot but it currently creates a URL, downloads a table, converts it, and displays it in a sheet.

The next issue (which is the major one for now) is that I can't figure out how to create the fifth column in my sales table that looks up the date and the local currency, then finds the corresponding rate in the FX table, then multiplies that by the price. This would be an insanely easy task in excel but my DAX skills are sorely lacking. I did find this web page which has a solution of sorts, but this doesn't use a different rate for every day: Prodata Blog | Many to Many Currency Conversion with PowerPivot

If anyone could help me or nudge me in the right direction I'd appreciate it. And if anyone has a solution to getting Oanda rates into powerpivot then that would be brilliant too!

I'm not sure if replying to myself is the done thing on here but I found a solution of sorts. In essense the solution was to structure the data in a different way. Instead of having dates in column A and then different currencies in columns B, C, D, E, etc... I had to create 3 columns. Column A contained the date, column B contained the currency code (ie. AUD, USD, DKK, etc), and column C contained the rate. Once the data was in this structure I was able to use lookupvalue on both the date and the currency symbol.

So, in another table I had foreign currency, date, and currency symbol. I then created another calculated column that looked up the exchange rate for that particular date and currency symbol and converted the foreign currency into local currency.

The only remaining painful part is that I have to semi manually update the daily fx rates. I'm doing this using the macro in the spreadsheet i linked to above. If anyone has a better suggestion I'm all ears!

I've also enquired about paying oanda for a data feed to make my life a bit easier!

 

[Tuta]

I'm curious if your macro still works correctly with Oanda? I've attempted the macro at the linked page here, but I cannot seem to get it to cooperate...

Thanks!

 

[GDRIII]

You need a calendar table to link to your two tables together.

Then you will open up a can of time intelligence whoop a$$...

 

[ContinuumDan]

How about using Power Query to get the Oanda data and then use Power Query to build a cross rate table to load into the data model ?

 

[Tuta]

I've had limited success with Power Query.

http://www.oanda.com/currency/historical-rates-classic?date_fmt=us&date=11/03/14&date1=1/1/14&exch=USD&exch2=USD&expr=GBP&expr2=GBP&margin_fixed=0&format=HTML&redirected=1

The above works to pull data. It places it in two separate data tables which makes it a little longer process to get a complete table to work with.

I'd also rather have the Bid price, but I can't see how to do so using this method of pulling data with PQ.



Since the fields for this query are really a part of the URL itself -- what would be an easier way to use this if I wanted to pull multiple currency sets? GBP, MXN, EUR, etc. compared with USD? What about easily modifying the date ranges pulled?

 

[ContinuumDan]

I've had limited success with Power Query.

http://www.oanda.com/currency/historical-rates-classic?date_fmt=us&date=11/03/14&date1=1/1/14&exch=USD&exch2=USD&expr=GBP&expr2=GBP&margin_fixed=0&format=HTML&redirected=1

The above works to pull data. It places it in two separate data tables which makes it a little longer process to get a complete table to work with.

I'd also rather have the Bid price, but I can't see how to do so using this method of pulling data with PQ.

Since the fields for this query are really a part of the URL itself -- what would be an easier way to use this if I wanted to pull multiple currency sets? GBP, MXN, EUR, etc. compared with USD? What about easily modifying the date ranges pulled?

Sorry, haven't had a chance to play.

The alternative is to communicate via the Web Services in Power Query.
Introduction | OANDA Exchange Rates API
Although it does look like it might cost money...

Ref your first point...
Even though Power Query does break it down into steps, the advantage of that is that you don't have to persist either of the intermediates either in the worksheet or data model, but only as a reference for your ultimate destination.
In the query list right click, Load To, and uncheck both check boxes.

Ref the date range, think you should be able to do this in M via a function, but haven't had enough time to play with that I am afraid.

As to how to get Bid instead, sorry not sure but looks like could be baked into the URL also if only we knew what the parameter name was.
Sorry not to be more help.

 

[Tuta]

As to how to get Bid instead, sorry not sure but looks like could be baked into the URL also if only we knew what the parameter name was.
Sorry not to be more help.

Is there a way to get a list of them?