# Using R for Exchange Rates

### I was trying to convert local prices of some items into Euros using a fixed exchange rate for all data. But as exchange rates fluctuate so much, this was incorrect.

· Big Data Zone · Tutorial
Save
4.97K Views

Today, I spent some time learning the R language.

The problem I was trying to solve was converting local prices of some items into Euros. I had been using a fixed exchange rate for all data, but as exchange rates fluctuate so much, this was incorrect.

My first thought was to find a free API that I could query to get the values I wanted. The first API I found didn’t cover all the currencies. The next one I found I burnt through the free allowance in one pass.

A colleague of mine mentioned using R to solve this. He sent me some links and I set out to write my first piece of R code.

My finished code can be found on GitHub and I will attempt to explain some of it.

R defines functions fairly simply:

``````nameoffunction <- function(arg1, arg2)
{
arg1 * arg2
}``````

I have created a function that takes two parameters: date and currency. I know I have about 10 different currencies that I want to get currencies for, and I want to loop through each day, so I will need to pass in a date.

The source of my exchange rate information is the www.xe.com website. Its historical exchange rate page passes currency and date into the query string, so I should be able to build up a string containing all the different elements.

All programming language can concatenate strings and R is no different. It uses `paste()`:

``var <- paste(“Hello”, “World”)``

However, R has an annoying feature in this function. I would expect that `var` in the above example would contain `HelloWorld`. It doesn’t. It contains `Hello World`. Why it automatically adds a space, I don’t know.

``var <- paste(“Hello”, “World”, sep=””)``

I am not entirely sure what all of the code does, but I can take a good guess.

`read_html()` , I would guess, loads an HTML page. `html_nodes()` finds all the HTML tags of a certain type on the page. In my case, `<table>`, `html_table()` reads the first table it finds.

`table1[2]` selects the second column, and `head()` selects a specific number of rows. I want the first row and second column so I combine these two as `head(table1[2],1)` .

Now that I have found my exchange rate, what do I do with it? R can read and write to SQL Server, so why not store this info in a SQL lookup table? I can then use this data in a stored procedure when I process my data.

To query SQL, you can use `sqlQuery()`. It has two parameters: a SQL connection and a TSQL command (i.e., a `SELECT`, `INSERT`, or `UPDATE` statement).

I use a `while` loop to loop through every day between October 1, 2016 and today, and look up the exchange rate for each currency.

For now, I am manually running this R script. However, there are ways to run R directly from SQL Server, which I may well investigate. I could then have a SQL job to run this on a schedule — maybe once a day to get the latest exchange rates. I also would like to do something a bit cleverer like only getting exchange rates for the days that I need them by querying existing database tables.

Topics:
big data, r language, data science, exchange rates, tutorial

Published at DZone with permission of Simon Foster, DZone MVB.

Opinions expressed by DZone contributors are their own.