Java Currency Converter using Yahoo Finance API – Currency Matrix

On the post Basic Java Currency converter using Yahoo Finance API I showed how currency exchange rate information can be easily acquired.

Getting a single exchange rate between two currencies is a nice thing, but it can be inefficient, if one would like to get several currencies exchange rate information. Since the Acquiring the information requires a request to Yahoo servers, making multiple requests in order to get several exchange rates is an expensive operation. Luckily, Yahoo API allows getting more than one exchange rate information on a single request.

The structure for getting more than one currency information at a single request is pretty straight forward and can be easily deduced from the basic request I was showing on the previous post:

http://download.finance.yahoo.com/d/quotes.csv?s=[From Currency][To Currency]=X&...&s=[From Currency][To Currency]=X&f=l1&e=.cs

For example, getting 3 currencies information on a single request for the following currency pairs:

• USD, ILS
• USD, JPY
• USD, GBP

Looks like:

http://download.finance.yahoo.com/d/quotes.csv?s=USDILS=X&s=USDJPY=X&s=USDGBP=X&f=l1&e=.cs

Extending our YahooCurrencyConverter from the previous post, to get currency information of more than one currency pair, is quite easy and therefore, I will jump directly to the main goal of this post: Getting currencies matrix information. Currency matrix information is a neat way of showing currency exchange rates of several currencies on a single table. Currency matrix can be illustrated easily by example. Currency matrix for the currencies: ILS, USD, GBP looks like:

         ILS     USD     GBP
ILS            0.247   0.170
USD    4.044           0.686
GBP    5.893   1.457        

In order to add currency matrix information, we will first do 2 things:

• Add a new class named: CurrencyPair. This class represents the relation between 2 currencies.
• Add a new method to the interface CurrencyConverter. This method defines a way of getting exchange rate information for several currencies.
  
  

Note, that the code in this post relies on the code from the post: Basic Java Currency converter using Yahoo Finance API.

This is the code of CurrencyPair class:

public class CurrencyPair {
    private String from;
    private String to;
    float price;
    public CurrencyPair(String from, String to)
    {
      this.from = from;
      this.to = to;
    }
    public String getFrom() {
        return from;
    }
    public void setFrom(String from) {
        this.from = from;
    }
    public String getTo() {
        return to;
    }
    public void setTo(String to) {
        this.to = to;
    }
    public float getPrice()
    {
        return price;
    }
}

This is the code of CurrencyConverter interface:

public interface CurrencyConverter {
    public float convert(String currencyFrom, String currencyTo) throws Exception;
    public void convert(CurrencyPair[] currencyPairs) throws Exception;
}

Now, we will add a new abstract class that will do the currency matrix calculations. Note that the currency matrix calculations relays on the new convert method added to CurrencyConverter interface:

public abstract class BaseCurrencyConverter implements CurrencyConverter {
    public CurrencyPair[][] getConversionMatrix(String... currencies) throws Exception {
        // Build pair combinations
        int size = currencies.length;
        CurrencyPair[] currencyPairs = new CurrencyPair[size * size];
        int index = 0;
        for (int i = 0; i < size; i++) {
            for (int j = 0; j < size; j++) {
                currencyPairs[index++] = new CurrencyPair(currencies[i], currencies[j]);
            }
        }
        // Get currencies information
        convert(currencyPairs);
        // Build matrix
        CurrencyPair[][] matrix = new CurrencyPair[size][size];
        index = 0;
        for (int i = 0; i < size; i++) {
            for (int j = 0; j < size; j++) {
                matrix[i][j] = i != j ? currencyPairs[index] : null;
                index++;
            }
        }
        return matrix;
    }
}

The method getConversionMatrix in the abstract class BaseCurrencyConverter builds currency matrix information for any class implementing the convert method. It does not care about how the convert method is implemented. The method receives a list of currencies for which we would like to build the matrix. From the list it builds all combinations between any 2 currencies. After building list of combinations, the abstract convert method is activated, to get the actual currency information. The final part of the class arranges the results in a 2 dimensional array, for easier data access.

Finally, after laying the structure, we can go back to the main implementing class: YahooCurrencyConverter. We don’t really have much more work to do, since the matrix creation is done on the abstract class. We just have to implement the new added convert method, to allow getting currency information for several currencies at a single request. In addition the class contains a small main program showing how the currency matrix can be used:

public class YahooCurrencyConverter extends BaseCurrencyConverter {
    public float convert(String currencyFrom, String currencyTo) throws IOException {
        HttpClient httpclient = new DefaultHttpClient();
        HttpGet httpGet = new HttpGet("http://quote.yahoo.com/d/quotes.csv?s=" + currencyFrom + currencyTo + "=X&f=l1&e=.csv");
        ResponseHandler<String> responseHandler = new BasicResponseHandler();
        String responseBody = httpclient.execute(httpGet, responseHandler);
        httpclient.getConnectionManager().shutdown();
        return Float.parseFloat(responseBody);
    }
    public void convert(CurrencyPair[] currencyPairs) throws IOException {
        HttpClient httpclient = new DefaultHttpClient();
        StringBuffer sb = new StringBuffer();
        for (CurrencyPair currencyPair : currencyPairs) {
            sb.append("s=").append(currencyPair.getFrom()).append(currencyPair.getTo()).append("=X&");
        }
        HttpGet httpGet = new HttpGet("http://quote.yahoo.com/d/quotes.csv?" + sb + "f=l1&e=.csv");
        ResponseHandler<String> responseHandler = new BasicResponseHandler();
        String responseBody = httpclient.execute(httpGet, responseHandler);
        httpclient.getConnectionManager().shutdown();
        String[] lines = responseBody.split("\n");
        if (lines.length != currencyPairs.length) {
            throw new IllegalStateException("Currency data mismatch");
        }
        int i = 0;
        for (String line : lines) {
            CurrencyPair currencyPair = currencyPairs[i++];
            currencyPair.price = Float.parseFloat(line);
        }
    }
    public static void main(String[] args) {
        YahooCurrencyConverter ycc = new YahooCurrencyConverter();
        try {
            String[] currencies = new String[] { "USD", "EUR", "GBP", "JPY", "CHF", "CAD", "AUD", "MXN", "ILS" };
            CurrencyPair[][] currencyPairs = ycc.getConversionMatrix(currencies);
            System.out.print("    ");
            for (int i = 0; i < currencyPairs.length; i++)
            {
                System.out.print("     " + currencies[i]);
            }
            System.out.println();
            for (int i = 0; i < currencyPairs.length; i++)
            {
                for (int j = 0; j < currencyPairs.length; j++)
                {
                    if (j == 0)
                    {
                        System.out.print(currencies[i] + " ");
                    }
                    CurrencyPair currencyPair = currencyPairs[i][j];
                    if (currencyPair != null)
                    {
                        System.out.printf("%8.3f", currencyPair.price);
                    }
                    else
                    {
                        System.out.print("        ");
                    }
                }
                System.out.println();
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

This is how the output for the currency matrix looks like:

         USD     EUR     GBP     JPY     CHF     CAD     AUD     MXN     ILS
USD            0.733   0.686  96.940   1.125   1.223   1.419  14.275   4.041
EUR    1.363           0.936 132.178   1.534   1.668   1.935  19.464   5.510
GBP    1.457   1.069         141.246   1.639   1.782   2.068  20.799   5.888
JPY    0.010   0.008   0.007           0.012   0.013   0.015   0.147   0.042
CHF    0.889   0.652   0.610  86.177           1.087   1.262  12.690   3.592
CAD    0.817   0.600   0.561  79.245   0.920           1.160  11.669   3.303
AUD    0.705   0.517   0.484  68.309   0.793   0.862          10.059   2.848
MXN    0.070   0.051   0.048   6.791   0.079   0.086   0.099           0.283
ILS    0.248   0.182   0.170  23.989   0.278   0.303   0.351   3.533        

I hope this code will help you to easily build your own custom currency matrices. This information may be useful to currency traders or applications that need to show the latest exchange rate between currencies.

As stated for the previous post, this class is making use of the Apache open source project: Http Client. You will need to put in your project the proper jar files for this class to work properly.

Basic Java Currency converter using Yahoo Finance API

Yahoo Finance is offering a very nice currency converter. It can show conversion rates for many currencies and even show historical currency information. Since yahoo converter is a web page, we are unable to access directly to the currency conversion rates. Yahoo does not offer a well defined API for accessing its currency information, but luckily, it does supply some basic capability for achieving this task. Yahoo claims that the data supplied is not “bank rates”, but I believe it is pretty much accurate data, that can be used for many applications that doesn’t demand exact “real time” information.

Yahoo API is very simple. The basic general request for getting the current currency rate between two currencies looks like:

http://download.finance.yahoo.com/d/quotes.csv?s=[From Currency][To Currency]=X&f=l1&e=.cs

For example, in order to get the current currency rate between US Dollars and Israeli Shekels, the following request should be constructed:

http://download.finance.yahoo.com/d/quotes.csv?s=USDILS=X&f=l1&e=.cs

As can be noticed, the parameter “s” contains the two currencies for which we would like to know the current rate. “UDS” is the currency symbol of US Dollars and “ILS” is the currency symbol for Israeli Shekels.

As was said before, Yahoo didn’t make a proper API for getting the currency information (like XML). The response for this request is a CSV file containing a single value, which is the currency rate.

Getting the currency rate information is pretty straight forward. I made a small Java class that does the job. It starts with a basic interface to define a general converter behavior:

public interface CurrencyConverter {
    public float convert(String currencyFrom, String currencyTo) throws Exception;
}

And the implementing class with a basic main application showing its usage:

public class YahooCurrencyConverter implements CurrencyConverter {
    public float convert(String currencyFrom, String currencyTo) throws IOException {
        HttpClient httpclient = new DefaultHttpClient();
        HttpGet httpGet = new HttpGet("http://quote.yahoo.com/d/quotes.csv?s=" + currencyFrom + currencyTo + "=X&f=l1&e=.csv");
        ResponseHandler<String> responseHandler = new BasicResponseHandler();
        String responseBody = httpclient.execute(httpGet, responseHandler);
        httpclient.getConnectionManager().shutdown();
        return Float.parseFloat(responseBody);
    }

    public static void main(String[] args) {
        YahooCurrencyConverter ycc = new YahooCurrencyConverter();
        try {
            float current = ycc.convert("USD", "ILS");
            System.out.println(current);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Note, that this class is making use of the Apache open source project: Http Client. You will need to put in your project the proper jar files for this class to work properly.

Generating a sequence of numbers using plain SQL query

SQL queries are usually used to get information from one or more table(s). But there are times it is useful to simply generate a sequence of numbers. The numbers can be joined with some another table, to create a complete list.

For example, suppose we have a log table named “log” containing only 2 columns: the day of the year starting from 0 and some counted value for that day. The 2 columns are named: “day” and “count”.

For things to be easy, lets assume the days in the log table can be from “0” to “99”.

Note, that there may be days in the log table that are not counted at all. For example, the log may look like:

Day	Count
0	345
1	434
3	255
8	345
13	445

And so on…

Now, suppose we need to output a report, showing the entire log table, including days that has no counts in the log. That means, we need somehow to fill the “gaps” in the log table in order to show a full list of days from “0” to “99”.

In order to produce the report, we need to create a table containing a sequence of numbers from “0” to “99” and than join that table with our “log” table.

Generating a sequence of numbers in plain SQL query is not a trivial task, but it can be done, by taking advantage of the SQL UNION command and using the SQL property: Cartesian join also known as Cross join.

The idea is to create 2 tables:

• One table contains numbers from 0 to 9.
• Second table contains numbers: 0, 10, 20, … 90.

Then, cross join the tables and sum the join products.

The cross join looks like:

Table 1	Table 2	Sum
0	0	0
1	0	1
…	…	…
9	0	9
0	10	10
1	10	11
…	…	…
9	10	19
0	20	20
1	20	21
…	…	…
9	20	29

And so on until 99…

The SQL query looks like:

select t1.x + t2.x
from
(select 0 as `x` union
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9) as t1,
(select 0 as `x` union
select 10 union
select 20 union
select 30 union
select 40 union
select 50 union
select 60 union
select 70 union
select 80 union
select 90) as t2
order by 1

Now, in order to show complete list of days and log counts for the log table in the example:

select all_days.day, log.count from
(select t1.x + t2.x as 'day'
from
(select 0 as `x` union
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9) as t1,
(select 0 as `x` union
select 10 union
select 20 union
select 30 union
select 40 union
select 50 union
select 60 union
select 70 union
select 80 union
select 90) as t2
order by 1) as all_days left join log on all_days.day = log.day

Suppose you want a sequence of numbers that is different from 0 to 99. For example, you want a range of numbers between 450 to 480. You can easily change the query (the first one) to do it, by using simple add operation and using the WHERE clause:

select 450 + x from
(select t1.x + t2.x as `x`
from
(select 0 as `x` union
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9) as t1,
(select 0 as `x` union
select 10 union
select 20 union
select 30 union
select 40 union
select 50 union
select 60 union
select 70 union
select 80 union
select 90) as t2
order by 1) as `numbers_sequence` where 450 + x <= 480

You can use this trick also if you need to generate a range of dates. All you have to do is to use the specific function of your SQL engine (mySQL, SQLServer etc’) that allows adding number of days to a date.

It is important to mention, that there are more solutions that can achieve the same effect:

• Add the missing data in a stored procedure code.
• Add the missing data in a your programming language code (Java, PHP, etc’).

Every method has its own advantages and disadvantages: Stored procedures are not always available, but very efficient and allows you to “speak” directly in the database terminology. Adding the data in your programming language may be a bit more complex. You also don’t always have the privilege of manipulating the data in a programming language. For example, if you are using a generic reporting system, that automatically plots the data of a Resultset. From the other hand, as was said before: you don’t always have the option of using stored procedures. If both: stored procedures and programming language are not available, you can always use the query on this example. It is simple, fast and leaves the solution on the domain of the SQL engine.