Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
29. September 2009 16:02

I had an Eureka moment today, and realized that I was trying hard to resolve the wrong problem, when what was needed was a change of frame.

I have been developing an application which tracks differences between Excel worksheets, and displays them in a layout similar to Excel itself. The user can navigate through the sheet on display by using two scrollbars, just like he would with Excel.

To achieve this, my approach so far has been to read the data, create a grid using WPF, adding one cell control to the grid for each cell in the spreadsheet, and navigate “over” that grid using the scroll bars.

This approach works well as long as the spreadsheet is small, but for larger sheets – say, 200 x 200 cells - the time needed to create the cells and add them to the grid was getting pretty long, so I focused my attention on figuring out a way to populate the grid faster, without much success.

Then yesterday, someone wrote this on StackOverflow:

I'm going to go out on a limb here and say that trying to add 40,000 controls is your real bottleneck; not so much as to how you're adding the controls.

Indeed – that’s completely correct.This didn’t answer my issue directly, but it has been liberating, and got me to rethink the question from a different angle – do I really need 40,000 cells?

More...

20. September 2009 07:10

I was recently inspired by an article in OR/MS mag to write 2 posts on using Excel data tables to resolve optimization problems. As it turns out, these puzzles are not only published in the magazine: they also have their online home at Puzzlor.com. So if you like optimization and math problems in general, and like puzzles, check it out!

18. September 2009 06:12

I found a bug in my code the other day. It happens to everybody - apparently I am not the only one to write bugs – but the bug itself surprised me. In my experience, once you know a piece of code is buggy, it’s usually not too difficult to figure out what the origin of the problem might be (fixing it might). This bug surprised me, because I knew exactly the 10 lines of code where it was taking place, and yet I had no idea what was going on – I just couldn’t see the bug, even though it was floating in plain sight (hint: the pun is intended).

Here is the context. The code reads a double and converts it into a year and a quarter, based on the following convention: the input is of the form yyyy.q, for instance, 2010.2 represents the second quarter of 2010. Anything after the 2nd decimal is ignored, 2010.0 is “rounded up” to 1st quarter, and 2010.5 and above rounded down to 4th quarter.

Here is my original code:

public class DateConverter
{
public static int ExtractYear(double dateAsDouble)
{
int year = (int)dateAsDouble;
return year;
}

public static int ExtractQuarter(double dateAsDouble)
{
int year = ExtractYear(dateAsDouble);
int quarter = (int)(10 * (Math.Round(dateAsDouble, 1) - (double)year));
if (quarter < 1)
{
quarter = 1;
}
if (quarter > 4)
{
quarter = 4;
}
return quarter;
}
}

Can you spot the bug?

More...

7. September 2009 13:14

It’s that time of the year again: Silicon Valley Code Camp is coming up on October 3rd and 4th, at Foothill College. If you live in the Bay Area and like to talk code, this is an event you don’t want to miss. The previous editions rocked, and this year looks like it’s going to rock even harder, with well over 100 sessions and close to 1,000 registered! Oh, and did I mention it’s free?

This year again, I will give an introduction to Test-Driven Development for .NET developers. It’s a topic which is dear to my heart; in his book on TDD, Kent Beck says that it “is a way of managing fear during programming”, and I have to say that my life as a developer got significantly more peaceful after reading it. I can’t guarantee that you will feel the same, but I’ll do my best to share the goods!

The session is targeted for beginners. My goal is to get you quick-started so that you are ready to use it when you leave the room. I will write some code live, to show the methodology in action, using only tools you can get for free. This year, I think I will focus mostly on NUnit and keep it to a minimum on the tools provided in Visual Studio, unless there is strong popular demand; hopefully this will give me enough time to squeeze in a few minutes on mocks. I also just ordered Roy Osherove’s “The Art of Unit Testing”, which looks very promising, and may push me to modify my plan a bit.

I hope to see you there – and if you have questions or suggestions, let me know in the comments section!

3. September 2009 17:50

In my last post, I illustrated how to quickly to pick the best value from a selection to get the optimal result, by using Excel Data Tables. This time, we will see how to pick the best possible pair of values.

We are trying to figure out which 2 bridges we should build, in order to minimize the overall travel time for the inhabitants of the island.

I worked out the math for one bridge last time. We will start we a similar setup, but adjust our spreadsheet so that for each islander, we compute the travelling distance for 2 bridges, and select the shortest route.

The ranges B1 and B2 are named Bridge1 and Bridge2. Column I now contains the formula computing the shortest route for each islander. For row 5 for instance, the formula is

=MIN(D5+E5-H5,F5+G5-H5)

Cell I10 is the total of the vertical distances travelled by each individual.

We can select from 4 bridge locations: 2, 4, 7 and 12. What we need is to find out which 2 numbers give us the lowest total travel. Let’s build our data table, this time using 2 bridge positions.

More...

#### Need help with F#?

The premier team for
F# training & consulting.