Creating an Excel Stock Information RTD server using the Yahoo Finance API

Yahoo’s Query Language or YQL allows you to run SQL-like queries against a variety of web services. The amount of available web services are immense! To get a better idea of all the available services,   which services are on offer, and the type of data they return, have a look at the YQL Console.

In today’s article, we’ll write an Excel RTD server that returns the latest stock prices and other information. In case you’re not familiar with Excel RTD servers, it is used to communicate with a real-time data source, such as stock quotes and provide the user with a constant flow of changing data.

• Using the Yahoo Finance web-service
• Creating a new Excel RTD Server
• Integrating the Excel RTD server with the Yahoo API
• Using the RTD Server in Excel

Using the Yahoo Finance web-service

To retrieve this information we’ll use the data available in the yahoo.finance.quotes table.  First, we’ll need to see the data that resides in this table, by navigating to the YQL Console. In the top left corner, make sure the Show Community Tables checkbox is checked.

Next, scroll down to the yahoo group and click on yahoo.finance.quotes. The console will open a page with a demonstration on how to use the table, which includes a sample query. You have a choice to return the data in either XML or JSON.

Change the YQL statement to: select * from yahoo.finance.quotes where symbol=”MSFT” and click the Test button. The result should show the stock information for Microsoft:

At the bottom of the page you should see the Rest query which you can use to call the web-service by performing a HTTP GET request to the URL shown inside the Rest query textbox. In our example the URL is:

https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%3D%22MSFT%22&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback=

Creating a new Excel RTD Server

Now that we have an idea on the type of data to expect from the YQL finance tables, we can create an Excel RTD server to implement the Yahoo service. First, use Add-in Express for Office and .net to create a new RTD Server project in Visual Studio.

Select your programming language of choice. For this example, we’ll be creating a Visual C# Project, you can also write in VB.NET or Visual C++.

After the “New Microsoft Excel RTD Server” project wizard has completed, double-click on the RTDServerModule.cs class to open its design surface. Set its Interval property to 600000, which will cause the data returned by our RTD server to be updated every 10 minutes.

Right-click on the designer surface and select Add RTD Topic from the context-menu.

Select the newly added RTD Topic component and set its properties to the following:

• Name : rtdTopicQuotes
• String01 : *
• String02 : *
• UseStoredValue : True

Next, click on the Events button on the Properties window toolbar, and generate an event handler for the RTD Topic’s RefreshData event by double-clicking next to the event name inside the Properties window.

Inside the event handler method for the RTD Topic, add the following code:

private object adxrtdTopic1_RefreshData(object sender)
{
    if (sender is ADXRTDTopic)
    {
        ADXRTDTopic topic = sender as ADXRTDTopic;
        string symbol = topic.String01;
        string fieldName = topic.String02;
        return GetValue(symbol, fieldName);
    }
    return default(object);
}

Integrating the Excel RTD server with the Yahoo API

The preceding code will retrieve the stock symbol as well as the field name to display from the RTD formula inside Excel and in turn call the GetValue method with the two values. The code for the GetValue method follows below:

private string GetValue(string symbol, string field)
{
    string json = HttpGet(symbol, field);
    dynamic obj = JsonConvert.DeserializeObject(json);
    var quote = obj.query.results.quote;
 
    JToken returnValue = null;
    quote.TryGetValue(field, out returnValue);
    if (returnValue != null)
        return returnValue.Value();
 
    return field + " is not a valid field name.";
}

The GetValue method receives the stock info data for the passed in symbol in JSON format and uses the Newtonsoft.Json library to convert the data into a dynamic object. We then use the TryGetValue method to get the value of the field name, passed in as a parameter, from the JSON data. Finally, we return the data if the field is valid, but if the field is not valid, we return an error message.

Note: To add the Newtonsoft.Json library to your project, open the Package Manager Console, by selecting it from the Tools > NuGet Package Manager menu. Type the following command in the console:

Finally, we use the HttpGet method to perform an HTTP GET request to the Yahoo Finance API. The code for the HttpGet method is as follows:

private string HttpGet(string symbol, string field)
{
    string uri = string.Format("https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol=%22{0}%22&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys", symbol);
 
    var req = WebRequest.Create(uri);
    var resp = req.GetResponse();
    var sr = new System.IO.StreamReader(resp.GetResponseStream());
    return sr.ReadToEnd().Trim();
}

Using the RTD Server in Excel

We’re now ready to test our Excel RTD Server and see whether it returns real-time data from Yahoo Finance. Build, register and run your project. Inside Excel, we’ll use the following formula:

=NUMBERVALUE(RTD("excelstockinfo.rtdservermodule","",B2, $C$1))

The formula above, calls the Excel Real-Time Data server we’ve just created and passes in the symbol, which is stored in the B2 column. We get the field name to return from the value stored in the column of the first row, which in this case is the AskRealtime field.

We also convert the returned value to a number using the NUMBERVALUE Excel formula – this is to enable us to add an Excel graph that uses the values. The final result inside Excel could look similar to the following screenshot:

Thank you for reading. Until next time, keep coding!

Available downloads:

This RTD server was developed using Add-in Express for Office and .net:

Excel Stock sample RTD server (C#)

You may also be interested in:

Calling Yahoo Weather web-service from an Outlook add-in