Graph currency rates over time in C#

[graph currency rates]

This example, which shows how to graph currency rates for a specific currency, is a bit longish. Sorry about that. It uses Yahoo Query Language (YQL), which you can use to perform a wide variety of queries on Yahoo’s data.

The example Get currency exchange rates in C# shows how to get the exchange rates for all of the currencies available from finance.yahoo.com. This example lets you fetch historical data to graph currency rates for a particular currency over time.

Enter a currency symbol (use the previous example to learn what symbols are available), start dates, and end dates. When you click Graph, the following code executes.

// Get the data and graph it.
private void btnGraph_Click(object sender, EventArgs e)
{
    // Compose the query URL.
    string base_url =
        "https://query.yahooapis.com/v1/public/yql?" +
        "q=@QUERY@" +
        "&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys" +
        "&format=xml";
    string query = "SELECT Date, Close " +
        "FROM yahoo.finance.historicaldata " +
        "WHERE symbol = '@CURRENCY@=X' " +
        "AND startDate = '@START@' " +
        "AND endDate = '@END@' " +
        "| sort(field='Date')";
    query = query.Replace("@CURRENCY@",
        txtCurrency.Text.Trim().ToUpper());
    query = query.Replace("@START@", txtStartDate.Text);
    query = query.Replace("@END@", txtEndDate.Text);

    string url = base_url.Replace("@QUERY@", query.UrlEncode());
    //Console.WriteLine(query);
    //Console.WriteLine(url);

    // Load the XML result.
    XmlDocument doc = new XmlDocument();
    doc.Load(url);

    // Get the data.
    PriceList = new List<PriceData>();
    XmlNode root = doc.DocumentElement;
    string xquery = "descendant::quote";
    foreach (XmlNode node in root.SelectNodes(xquery))
    {
        string date_text =
            node.SelectSingleNode("descendant::Date").InnerText;
        DateTime date = DateTime.Parse(date_text);

        string close_text =
            node.SelectSingleNode("descendant::Close").InnerText;
        decimal close = decimal.Parse(close_text);
        
        PriceList.Add(new PriceData(date, close));
    }

    // Graph the data.
    DrawGraph();
}

The code first composes a URL that includes a YQL query of the form:

SELECT Date, Close
FROM yahoo.finance.historicaldata
WHERE symbol = 'JPY=X'
AND startDate = '2016-11-01'
AND endDate = '2016-12-01'
| sort(field='Date')

The code calls the UrlEncode extension method described in the post Make string extensions to URL encode and decode strings in C# to replace special characters in the query with URL-safe characters. It then inserts the query into the URL it needs to execute to fetch the data.

The code then creates an XmlDocument and loads it from the URL. That fetches the data and saves it in the document. All the program needs to do now is take the data from the XmlDocument and graph it.

The code gets the document’s root node and uses its SelectNodes method to find the quote elements contained in the document. The following text shows what one of those elements looks like.

<quote>
  <Close>104.82</Close>
  <Date>2016-11-01</Date>
</quote>

Now the code uses the quote node’s SelectSingleNode method to find the Close and Date elements. It saves the price and date data in a PriceData object and adds that object to the PriceList list.

The PriceData class simply holds a price and a date. The PriceList variable is just a list of those objects.

Finally this method calls the following DrawGraph method.

// Draw the graph.
private Bitmap GraphBm = null;
private void DrawGraph()
{
    if (PriceList.Count < 1)
    {
        picGraph.Image = null;
        WtoDMatrix = null;
        DtoWMatrix = null;
        return;
    }

    int wid = picGraph.ClientSize.Width;
    int hgt = picGraph.ClientSize.Height;
    GraphBm = new Bitmap(wid, hgt);
    using (Graphics gr = Graphics.FromImage(GraphBm))
    {
        gr.SmoothingMode = SmoothingMode.AntiAlias;
        gr.Clear(Color.White);

        // Scale the data to fit.
        int num_points = PriceList.Count;
        float min_price = (float)PriceList.Min(data => data.Price);
        float max_price = (float)PriceList.Max(data => data.Price);
        const int margin = 10;

        WtoDMatrix = MappingMatrix(
            0, num_points - 1, min_price, max_price,
            margin, wid - margin, margin, hgt - margin);
        gr.Transform = WtoDMatrix;

        DtoWMatrix = WtoDMatrix.Clone();
        DtoWMatrix.Invert();

        // Draw the graph.
        using (Pen pen = new Pen(Color.Black, 0))
        {
            // Draw tic marks.
            PointF[] pts = { new PointF(10, 10) };
            DtoWMatrix.TransformVectors(pts);
            float dy = pts[0].Y;
            float dx = pts[0].X;

            for (int x = 0; x < PriceList.Count; x++)
            {
                gr.DrawLine(pen, x, min_price, x, min_price + dy);
            }
            for (int y = (int)min_price; y <= (int)max_price; y++)
            {
                gr.DrawLine(pen, 0, y, dx, y);
            }

            // Get a small distance in world coordinates.
            dx = Math.Abs(dx / 5);
            dy = Math.Abs(dy / 5);

            // Draw the data.
            PointF[] points = new PointF[num_points];
            for (int i = 0; i < num_points; i++)
            {
                float price = (float)PriceList[i].Price;
                points[i] = new PointF(i, price);
                gr.FillRectangle(Brushes.Red,
                    i - dx, price - dy, 2 * dx, 2 * dy);
            }
            pen.Color = Color.Blue;
            gr.DrawLines(pen, points);
        }
    }

    // Display the result.
    picGraph.Image = GraphBm;
}

The method starts by checking whether it has any data that it can use to graph currency rates. If it doesn't, it sets the PictureBox object's image and the program's two transformation matrices (described shortly) to null and returns.

Next the method creates a bitmap to fit the PictureBox. It creates a matrix to map points in the data onto the bitmap and saves the matrix in the variable WtoDMatrix. (Notice the use of anonymous methods to get the minimum and maximum price values that are used to create the mapping.) It also inverts the matrix and saves the new version in variable DtoWMatrix.

The code then draws the graph. It uses the DtoWMatrix object's TransformVectors method to see how big 10 pixels on the bitmap is in world coordinates and uses that information to draw tic marks on the graph.

The code then loops through the price data to draw rectangles and make PointF objects for each data point. When it finishes the loop, the code draws lines connecting the points and displays the result.

The last interesting piece of the program executes when the mouse moves over the PictureBox.

// Display the data in a tooltip.
private int LastTipNum = -1;
private void picGraph_MouseMove(object sender, MouseEventArgs e)
{
    if (DtoWMatrix == null) return;

    // Get the point in world coordinates.
    PointF[] points = { new PointF(e.X, e.Y) };
    DtoWMatrix.TransformPoints(points);

    // Get the tip number.
    int tip_num = -1;
    if (points[0].X >= 0) tip_num = (int)points[0].X;
    if (tip_num >= PriceList.Count) tip_num = -1;

    if (LastTipNum == tip_num) return;
    LastTipNum = tip_num;

    string tip = null;
    if (tip_num >= 0) tip = PriceList[tip_num].ToString();
    tipData.SetToolTip(picGraph, tip);
    ShowDatePriceLines();
}

When the mouse moves over the graph, the MouseMove event handler uses the DtoWMatrix object to convert the mouse's location into world coordinates. It then looks up the the data entry in PriceList corresponding to that date value.

If the entry is not in the list (for example, the mouse is to the left or right of the data values), the code sets tip_num to -1 to indicate that the program should not display a tooltip. Otherwise it sets tip_num equal to the index of the entry in the data list that corresponds to the mouse position.

If the new value for tip_num is the same as the previous one (stored in LastTipNum), the program does nothing. Otherwise it makes a tooltip display the data's value on the PictureBox and calls the following ShowDatePriceLines method to draw lines on the graph showing the selected date and price.

// Draw date and price lines for the mouse position.
private void ShowDatePriceLines()
{
    if (LastTipNum < 0) return;

    Bitmap bm = (Bitmap)GraphBm.Clone();
    using (Graphics gr = Graphics.FromImage(bm))
    {
        gr.Transform = WtoDMatrix;
        PriceData data = PriceList[LastTipNum];
        using (Pen pen = new Pen(Color.Red, 0))
        {
            gr.DrawLine(pen, LastTipNum, 0, LastTipNum, 100000);
            float price = (float)data.Price;
            gr.DrawLine(pen, 0, price, 100*PriceList.Count, price);
        }
    }

    picGraph.Image = bm;
}

This method makes a clone of the basic graph image saved in GraphBm. It then draws the date and price lines on the clone and displays the result in the PictureBox. The original GraphBm bitmap remains unchanged so it will be ready to use the next time the mouse moves.

This is a fairly bare-bones graph and you could make it much fancier. For example, you could label the axes and data points as in the example Graph stock prices downloaded from the internet in C#.

I don't think Yahoo's financial data has information about converting between two currencies that don't include US dollars. For example, I don't know of a way to find a conversion from Japanese yen directly to British pounds. If you figure out how to do that, post it in a comment below. Meanwhile you can calculate a reasonable exchange rate by converting yen to dollars and then dollars to pounds.

Download the example to see additional details.


Download Example   Follow me on Twitter   RSS feed   Donate




About RodStephens

Rod Stephens is a software consultant and author who has written more than 30 books and 250 magazine articles covering C#, Visual Basic, Visual Basic for Applications, Delphi, and Java.
This entry was posted in finance, internet, mathematics, web and tagged , , , , , , , , , , , , , , , , , , , , , , , . Bookmark the permalink.

3 Responses to Graph currency rates over time in C#

  1. Alexey says:

    Good day, Rod.
    1. Could you retry this example. Unfortunately i cant get it working.
    I mean there is http request, but result field is empty. As for other examples i tried without YQL everything is fine. Many people starting undestand YQL have problems.
    At least i can tell base url is same as in yql samples, what happens later – looks tricky.

    2. To be honest i have more experience with finances. So i ll try to explain cross rates idea and i suppose why there is not cross rate in Yahoo. I think they are trying to be more or less fair and not to show incorrect (better say wide variation in cross rates or prices).
    I mostly worked with money market – direct prices for overnight borrowing, landing, currencies conversion , fixed income instruments more than 15 years ago.
    Normally why you call market maker or you have quotes from broker i.e. electronic boards – they are usually 2 sides: bid / ask. They game is to move prices to the right side to control your position and. For example 1.4533/1.4536 – price with 3 pips spread and it may be shown as 33/36 for quik reply. So for example it was price for deutch mark (now it may be euro), and same way you may ask price for JPY: it will be 2 sides also. So for example you there is no market at the moment for usd/jpy, but you have prices 1.4533/1.4536 on one screen for one currency and on another you have ready to trade price 0.9868/0.9871. And you want to sell usd and buy last currency – what you have to compute: sell at 1.4533 and buy at 0.9871.
    Good question – where is the real price through cross rates: bid / ask:
    bid (for currency through cross rate) = 1*1.4533/0.9871
    ask 1/(1.4536 *0.9861)
    So i suppose it becomes not fair enough to show as indicative price or rate because yahoo or google cant know anything about real trades and show only prices of occured trades in “close” field.
    As for my opinion it is really lying especially for those who love technical analysis and chart painting.
    Certainly behaviour may be changed but i suppose there are a lot of additional explanations have to be added, and disclaimers of responsibilities limitations explained will become bigger.

    • RodStephens says:

      You’re right. This is just an estimate. Actual prices change too quickly and the data available for free is too limited for this kind of program to keep up. I wouldn’t use this program for serious investment analysis. I’ve found it useful when I’m going to travel somewhere and I want to know if rates are trending upward or downward.

      It seems that the program has stopped working, probably because of changes to Yahoo’s financial services. I’ll try to fix it when I have a chance.

  2. Alexey says:

    Small addition.
    In example there is assumptions:
    – there is no direct quote from usd to jpy, but there are 2 quotes usd/dem(euro if you want) and dem(euro)/jpy. That means you have to buy euro versus usd, sell euro versus jpy.
    [0 step]: you are long usd 1 mio
    [1 step]; after euro bying – you just sold 1 mio use, so you are zero position in usd, long in euro X amount
    [2 step]; you are zero in euro and long Y in jpy, 0 position in dollars
    Foreign exchange markets are mostly over the counter i.e. interbank and financial company sometimes. So even if yahoo or google is showing some money rates or currencies it is not true and not fair. As for stocks, bonds, etf, derivatives – they are traded mostly electronically – now nyse floor trading is mostly advertisement, so prices are looking more or less fair and even showing some traded volume in “close” fields. As you know Open High Close is just processed traded volume, usually taken from “close”. But this field in reality may be lying too. What if there is not trading at all? what has to be shown in close on next time period? Absolutely unfair and doesnt reflect anything than at the time t or earlier there could be some trading at this level. What to do with exchange rates or currencies if they are mostly interbank? These markets are mostly traded through reuters dealing (something similar to skype and in reality it is modern version of telex) in form i explained earlier and from time to time prices may be seen in reuters money terminal. I suppose they are taken automatically from brokers like tradition, tullett tokyo, but nobody needs to get them online through yahoo or other information retrieval business.
    Btw that’s why no one math guy never could find any reliable model to predict future prices from data of such quality.
    But if currencies are traded electronically – it happens on some exchanges sometime – yes you can get these prices. At least i know only one exchange who provides currencies and derivatives on currencies for trading. And it is not in USA.

Leave a Reply

Your email address will not be published. Required fields are marked *