Fast prototyping with C# and Excel

Excel doesn’t have much of a reputation as a developer tool. But imagine if instead of VBA you could use C#. Would this change things? I guess it depends. Admittedly, I’m a bit biased (since I wrote QueryStorm), but I find the combination of C# and a spreadsheet really useful. It lets me quickly build little self contained applications for all sorts of things. I write the logic in C# and use Excel for data storage, user interface and graphs. And, with LINQ support for Excel tables, I have a pretty easy time working with the data there.

That’s essentially what QueryStorm does: it turns Excel into a useful developer tool.

The example application: Exchange rates visualizer

For this example, I’m going to build an application for visualizing currency exchange rates over time.

Demo application screenshot

Please feel free to download the sample workbook and QueryStorm itself if you haven’t already.

Fetching the data

First thing’s first. To visualize anything, we first need to fetch the exchange rates data from somewhere. There’s plenty of exchange rate APIs to choose from. For this demo, I’ll use Fixer which is the first one I’ve found. It’s free and I liked it a lot.

The Fixr API accepts a few parameters (e.g. date and base currency), and returns a JSON response containing the exchange rates for a chosen date and base currency.

For an example response, go to: https://api.fixer.io/2018-03-16?base=USD

Let’s wrap this API in a nice C# function, so we can easily call it:

using Newtonsoft.Json.Linq;

public static IEnumerable<Rate> GetExchangeData(DateTime date, string baseCurrency = "EUR")
{
    var response = new WebClient()
        .DownloadString($"https://api.fixer.io/{date.ToString("yyyy-MM-dd")}?base={baseCurrency}");

    string ratesStr = JObject.Parse(response).Property("rates").Value.ToString();

    return JsonConvert
        .DeserializeObject<IDictionary<string, decimal>>(ratesStr)
        .Select(kvp => new Rate() { Currency = kvp.Key, Exchange = kvp.Value });
}

public class Rate
{
    public string Currency { get; set; }
    public decimal Exchange { get; set; }
}

The function hits the web API, and unpacks the JSON response into a collection of Rate objects. We can now use it to load the exchange rates for a given data and base currency.

We can use the code below to get the exchange rates for the past 36 months.

var data = Enumerable
    .Range(1, 36) //generate 36 integers
    .Select(i => DateTime.Today.AddMonths(-i)) //turn the integers into dates one month apart
    .SelectMany(dt => //project the date/currency/rate list
    { 
        return GetExchangeData(dt, "USD")
            .Select(ed => new 
            { 
                Date = dt, 
                Currency = ed.Currency, 
                Rate = ed.Exchange 
            });
    });

And here are the results:

Excchange rate date

Storing the data

Normally, we’d have to define a database to store this data if we wanted to analyze it or work with it later. We’d have to define the table schema, prepare the connection string, and make sure the database is available to the application. Then, in our application, we’d have to write a repository or data access layer or whatnot to let the application read and write data.

Since I’m in Excel, I’ll just save the data right into the sheet and skip all that nonsense. This is a prototype after all and not an enterprise application.

It’s ok, really, no one needs to know, it’ll be our little secret. The data will be stored in an Excel table and we’ll be able to query and process it using LINQ, all without having to write a single line of code or setting up any external infrastructure. And we’ll be able to use Excel to visualize the data.

To save the data as a table, I’ll create a new sheet and write the data as new table using the context menu.

Demo application

This will create the new table, and write the results into it. I can give it a name, e.g. exchangeRates. If I later want to update the table with fresh data, I’ll just use the IntoTable extension method:

data.IntoTable("exchangeRates")

This will overwrite the contents of the Excel table with the new data. If we like, we can hide this sheet from the user.

Visualization

In order to visualize the exchange rates, I’ll insert a pivot chart based on the data in the Excel table.

Demo application

When you create a pivot chart in Excel, a pivot table is created first, and the chart is data-bound to that pivot table. When ever the pivot table changes, the chart automatically updates as well. However, the pivot table itself does not automatically update when the source table is modified – it needs to be explicitly refreshed.

Instead of the user having to do that by hand, we can add a line of code to our script to refresh it automatically:

Workbook().GetPivotTable("PivotTable2").RefreshTable();

This will cause the pivot table to refresh, which in turn will cause the graph to update.

We can also move the pivot table to the sheet with the table if we don’t need to see it.

User interactions

Next step is interacting with the user, specifically: reading user input and reacting to user actions.

Reading and writing cell values

In this application, I’ll be reading just one parameter: the base currency. I’ll designate cell B1 for this. We can reference the cell by address, but a better way to reference it is to give it a name, e.g. baseCurr. That way, the cell can be moved without breaking the script.

string baseCurrency = Range("baseCurr").ValueString();

In future versions of QueryStorm, named ranges will likely be available to user scripts as variables.

Loading data might take a few seconds, so it’s also important to let the user know what’s going on. For this purpose, we’re going to output status messages into a designated cell with the name messages. We can do that like so:

Range("messages").Value = message;

And so, the final version of our script will be as follows:

#load "api\exchange rates"

try
{
    SetMessage("Working...");

    string baseCurrency = Range("baseCurr").ValueString();

    int numberOfMonths = 36;

    //fetch data
    var data = Enumerable
        .Range(1, numberOfMonths)
        .Select(i => DateTime.Today.AddMonths(-i))
        .SelectMany(dt => 
        { 
            return GetExchangeData(dt, baseCurrency)
                .Select(ed => new 
                { 
                    Date = dt, 
                    Currency = ed.Currency, 
                    Rate = ed.Exchange 
                });
        });

    //push results into table
    data.IntoTable(nameof(rates));

    //refresh pivot table
    Workbook().GetPivotTable("PivotTable2").RefreshTable();

    SetMessage("Done");
}
catch (Exception ex)
{
    SetMessage($"Failed...{ex.Message}");
}

void SetMessage(string message)
{
    Range("msgCell").Value = message;
}

This assumes that the GetExchangeData function is embedded into the workbook with the path api\exchange rates. We can embed this script into the workbook as well, with the path scripts\update

Reacting to user actions

All that’s left to do now is to set up automatic execution of the script in response to user actions. In the demo workbook, I’ve added an ActiveX button to the sheet for this purpose.

Once the button is there, a job with the appropriate trigger and action can be added via QueryStorm’s Automation pane.

Demo application

A slightly easier way to do it is from the context menu of the button.

Demo application

The finished product

And here’s what the finished product looks like:

Demo application

The entire project took me less than two hours to complete. A regular application with this level of functionality would have taken me days to build. The workbook is easy to share and requires no external infrastructure (except QueryStorm). So check it out, build something yourself and let me know what you build!

Leave a Comment

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