Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
14. January 2012 14:16

I am putting together a demo VSTO add-in for my talk at the Excel Developer Conference. I wanted to play with charts a bit, and given that I am working off a .NET model, I figured it would be interesting to produce charts directly from the data, bypassing the step of putting data in a worksheet altogether.

In order to do that, we simply need to create a Chart in a workbook, add a Series to the SeriesCollection of the chart, and directly set the Series Values and XValues as an array, along these lines:

var excel = this.Application;
var workbook = excel.ActiveWorkbook;
var charts = workbook.Charts;

chart.ChartType = Excel.XlChartType.xlLine;
chart.Location(XlChartLocation.xlLocationAsNewSheet, "Tab Name");

var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.NewSeries();

series.Values = new double[] {1d, 3d, 2d, 5d};
series.XValues = new string[] {"A", "B", "C", "D"};
series.Name = "Series Name";

This will create a simple Line chart in its own sheet – without any reference to a worksheet data range.

Now why would I be interested in this approach, when it’s so convenient to create a chart from data that is already in Excel?

Suppose for a moment that you are dealing with the market activity on a stock, which you can retrieve from an external data source as a collection of StockActivity .NET objects:

public class StockActivity
{
public DateTime Day { get; set; }
public decimal Open { get; set; }
public decimal Close { get; set; }
}

In this case, extracting the array for the X and Y values would be a trivial matter, making it very easy to produce a chart of, say, the Close values over time:

// Create a few fake datapoints
var day1 = new StockActivity()
{
Day = new DateTime(2010, 1, 1),
Open = 100m,
Close = 110m
};
var day2 = new StockActivity()
{
Day = new DateTime(2010, 1, 2),
Open = 110m,
Close = 130m
};
var day3 = new StockActivity()
{
Day = new DateTime(2010, 1, 3),
Open = 130m,
Close = 105m
};
var history = new List<StockActivity>() { day1, day2, day3 };

var excel = this.Application;
var workbook = excel.ActiveWorkbook;
var charts = workbook.Charts;

chart.ChartType = Excel.XlChartType.xlLine;
chart.Location(XlChartLocation.xlLocationAsNewSheet, "Stock Chart);

var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.NewSeries();

series.Values = history.Select(it => (double)it.Close).ToArray();
series.XValues = history.Select(it => it.Day).ToArray();
series.Name = "Stock";

Using LINQ, we Select from the list the values we are interested in, and pass them into an array, ready for consumption into a chart, and boom! We are done.

If what you need to do is explore data and produce charts to figure out potentially interesting relationships, this type of approach isn’t very useful. On the other hand, if your problem is to produce on a regular basis the same set of charts, using data coming from an external data source, this is a very interesting option!

26. December 2010 11:38

A little while ago, I looked into dynamically sorting a list, based on a criterion supplied by the user. There was one limit on the solution I presented, however: only one criterion could be applied at a time. What if we wanted to sort by multiple criteria?

For instance, using the same example as in the previous post, if we had a list of Products with a Name, a Supplier and a Price, how would we go about sorting by Supplier and then by Price? Or by Name and then Supplier? And how could we make that flexible, so that we can sort by an arbitrary number of criteria. This would be particularly useful if we had in mind the development of a user interface where users could select how they want to see a list of items displayed on screen.

Besides OrderBy, LINQ provides the ThenBy extension method. ThenBy is applied to an IOrderedEnumerable – a collection that has already been ordered – and sorts its, maintaining the existing order. For instance, in the following example, products are first sorted by Supplier, and for each Supplier, by Price:

private static void Main(string[] args)
{
var apple = new Product() { Supplier = "Joe's Fruits", Name = "Apple", Price = 1.5 };
var apricot = new Product() { Supplier = "Jack & Co", Name = "Apricot", Price = 2.5 };
var banana = new Product() { Supplier = "Joe's Fruits", Name = "Banana", Price = 1.2 };
var peach = new Product() { Supplier = "Jack & Co", Name = "Peach", Price = 1.5 };
var pear = new Product() { Supplier = "Joe's Fruits", Name = "Pear", Price = 2 };

var originalFruits = new List<Product>() { apple, apricot, banana, peach, pear };

Func<Product, IComparable> sortByPrice = (product) => product.Price;
Func<Product, IComparable> sortByName = (product) => product.Name;
Func<Product, IComparable> sortBySupplier = (product) => product.Supplier;

var sortedFruits = originalFruits
.OrderBy(sortBySupplier)
.ThenBy(sortByPrice);

foreach (var fruit in sortedFruits)
{
Console.WriteLine(string.Format("{0} from {1} has a price of {2}.",
fruit.Name,
fruit.Supplier,
fruit.Price));
}

}

More...

31. October 2010 12:36

Last week, an interesting problem came on my desk. Initially, when I was asked to sort items, I didn’t think much of it. Given a list of items, it’s fairly trivial to use LINQ and sort it by whatever property you want. What I hadn’t quite anticipated was that the user should be able to select between multiple sorting criteria.

If there was a predetermined sorting criterion, the problem would be straightforward. For instance, given a list of Fruits with a name, supplier and price, I can easily sort them by price:

static void Main(string[] args)
{
var apple = new Product() { Supplier = "Joe's Fruits", Name = "Apple", Price = 1.5 };
var apricot = new Product() { Supplier = "Jack & Co", Name = "Apricot", Price = 2.5 };
var banana = new Product() { Supplier = "Joe's Fruits", Name = "Banana", Price = 1.2 };
var peach = new Product() { Supplier = "Jack & Co", Name = "Peach", Price = 1.5 };
var pear = new Product() { Supplier = "Joe's Fruits", Name = "Pear", Price = 2 };

var originalFruits = new List<Product>() { apple, apricot, banana, peach, pear };

var sortedFruits = originalFruits
.OrderBy(fruit => fruit.Price);

foreach (var fruit in sortedFruits)
{
Console.WriteLine(string.Format("{0} from {1} has a price of {2}.",
fruit.Name,
fruit.Supplier,
fruit.Price));
}

}

Running this simple console application produces the following list, nicely sorted by price:

However, if we want to give the user to select how fruits should be sorted, the problem becomes a bit more complicated. We could write a switch statement, with something like “if 1 is selected, then run this sort, else run that sort, else run that other sort”, and so on. It would work, but it would also be ugly. We would be  re-writing essentially the same OrderBy statement over and over again, something which reeks of code duplication. How could we avoid that, and keep our code smelling nice and fresh?

More...

16. May 2010 08:14

I have been working with trees quite a bit lately, because I am coding something which involves probability trees: based on the state of the system, there is a number of things which can happen, each with a certain probability.

I ended up writing a simple generic Node class, which can contain anything, and can have multiple children, along these lines:

public class Node<T>
{
public Node()
{
this.Children = new List<Node<T>>();
}

public T Content
{
get;
set;
}

public List<Node<T>> Children
{
get;
private set;
}

public bool IsLeaf
{
get
{
return (this.Children.Count() == 0);
}
}
}

Pretty quickly, I realized I would need to get the list of all nodes under a certain node, as well as the list of its leaves (a leaf being a node that has no children, i.e. an endpoint of the tree). This is a job tailor-made for recursion: if a node is a leaf, return it, otherwise, search further in all his children.

More...

23. January 2010 00:21

One of the immediate benefits I saw in digging into F# is that it gave me a much better understanding of LINQ and lambdas in C#. Until recently, my usage of LINQ was largely limited to returning IEnumerable instead of List<T> and writing simpler queries, but I have avoided the more “esoteric” features. I realize that now that F# is becoming familiar to my brain, whenever I see a statement in C# which contains a foreach:

foreach (var item in items)
{
// do something with item.
}

… I ask myself if this could be re-written in a more functional way. Sometimes it works, sometimes not. Just like classic OO Design Patterns, functional programming has its own patterns, and I find that having a larger toolkit of patterns in the back of my mind helps criticizing my own code and think about alternatives and possible improvements.

I encountered one such case a few days ago, with the following snippet:

public bool IsValid()
{
foreach (var rule in this.rules)
{
if (!rule.IsSatisfied())
{
return false;
}
}

return true;
}

There is nothing really wrong with this code. However, seeing the foreach statement, and an if statement with a return and no else branch made me wonder how I would have done this in F# – and my immediate thought was “I’d use a Fold”.

More...

#### Need help with F#?

The premier team for
F# training & consulting.