I use LINQPad all the time for a bunch of stuff, but most recently and most relevant, I’ve been using it for a personal chore that isn’t developer-related: I’ve been using it to do budgeting.
What is LINQPad?
LINQPad is an interactive execution environment for LINQ queries, statements or programs. The typical usage model is that you point LINQPad at a SQL database or an OData endpoint via a dialog box and then start writing queries against the tables/collections exposed by that connection, e.g.
Here, you can see that I’ve added a connection on the left to the Northwind database, typed a query into the text box (I paid the $25 for the auto-completion module), executed the query and got the table of results below. If I want to operator over multiple results, including dumping them for inspection, I can do so by switch from C# Expression to C# Statements:
Notice the use of “Dump” to see results along the way. If I want to, I can switch to C# Program, which gives me a main and allows me to create my own types and methods, all of which can be executed dynamically.
To save queries, notice the “My Queries” tab in the lower left. I use this for things I run periodically, like the ads on my web site that are going to expire, some data cleanup I want to get back to and, the subject of today: budgeting.
Budgeting with Mint.com and LINQPad
For the uninitiated, mint.com is a free online personal financial management site. At its core, it uses financial account, loan and asset information that lets it log into various financial sites and grab my data for me, e.g. 1sttech.com, usbank.com, wcb.com, etc. It uses this to let me categorize transactions so that it can do budgeting for me. However, it doesn’t give me the control I want, so I write programs against this unified transaction information. Essentially, I re-categorize each transaction to my own set using a map I maintain in an Excel file, then compare the amount I spend each month against what my budget amount is, maintained in another sheet in that same Excel file. Because mint.com doesn’t provide a programmatic API (OData would be a godsend!), I pull down my transaction history as a CSV file that the web site provides for me, which I then translate to an Excel file.
Once I have these three Excel sheets, the translation history, the category map and the category budget amounts, I bring these pieces of data into my LINQPad script:
void Main() {
var mintExcel = ExcelProvider.Create(@"D:\data\finances\2010-08-25 mint transactions.xlsx");
var minDate = new DateTime(2010, 8, 1);
var txs = mintExcel.GetSheet<Tx>().Where(t=>t.Date>=minDate);
var debits = txs.Where(tx => tx.Type == "debit");
var classExcel = ExcelProvider.Create(@"d:\data\finances\2010-08-03 mint category map.xlsx");
var map = classExcel.GetSheet<CategoryClass>().ToList();
var classBudget = classExcel.GetSheet<ClassBudget>().ToList();
var unclassified = new ClassBudget() { Class = "UNCLASSIFIED" };
classBudget.Add(unclassified);
var classifiedDebits = debits.
Select(d => new { d.Date, d.Description, Amount = d.Amount, d.Category, Class = GetClass(map, d) }).
Where(d => d.Class != null);
// TODO: break this down by month
// TODO: sum this by ytd
var classifiedTotals =
from d in classifiedDebits
group d by d.Class into g
let b = classBudget.FirstOrDefault(b=>b.Class == g.Key) ?? unclassified
let total = g.Sum(d=>d.Amount)
select new {
Class = b.Class,
BudgetAmount = b.Amount,
ActualAmount = total,
AmountLeft = b.Amount - total,
TxCount = g.Count(),
Transactions = from tx in g.OrderBy(tx=>tx.Date)
select new { Date = tx.Date.ToString("M/d/yy"), tx.Description, tx.Category, tx.Amount }
};
classifiedTotals.OrderBy(d=>d.Class).Dump(2);
//classifiedTotals.OrderBy(d=>d.Class).Dump();
}
static string GetClass(List<CategoryClass> map, Tx tx) {
CategoryClass cc = map.FirstOrDefault(m => m.Category == tx.Category);
if( cc != null ) { return cc.Class; }
tx.Category.Dump("UNCLASSIFIED MINT CATEGORY");
return null;
}
[ExcelSheet(Name = "transactions(1)")]
public class Tx {
[ExcelColumn()] public DateTime Date { get; set; }
[ExcelColumn()] public string Description { get; set; }
[ExcelColumn()] public decimal Amount { get; set; }
[ExcelColumn(Name = "Transaction Type")] public string Type { get; set; }
[ExcelColumn()] public string Category { get; set; }
[ExcelColumn(Name = "Account Name")] public string AccountName { get; set; }
}
[ExcelSheet(Name = "Sheet1")]
public class CategoryClass {
[ExcelColumn()] public string Category { get; set; }
[ExcelColumn(Name="Classification")] public string Class { get; set; }
}
[ExcelSheet(Name = "Sheet2")]
public class ClassBudget {
[ExcelColumn(Name="Classification")] public string Class { get; set; }
[ExcelColumn()] public decimal Amount { get; set; }
public int Transactions { get; set; }
}
There are some interesting things to notice about this script:
- I needed to make it a full-fledged program so that I could define the shape of my data in Excel. LINQPad has no native support for Excel data, so I had modify an Excel LINQ provider I found on the interwebtubes. The types are needed to map the Excel columns to C# types so that I can query against them.
- This script isn’t pretty; it’s been built up over time and it works. I’ve been using it for a month and this month my task is to split it work across multiple months.
- I’ve built up error output over time to make sure I’m not dropping data in my queries. I spent an hour a coupla weeks ago tracking down 3 transactions.
- I’m doing slow look-ups cuz at the time I wrote this script, I wasn’t sure how to write joins in LINQ. It’s more than fast enough for my needs, so I’ve only dug into LINQ for accuracy, not efficiency.
LINQPad Output
By default, the output from my budgeting program looks like this (w/ my personal financial details blacked out):
Some things to notice:
- The output is spit into a table w/o me having to do anything except dump the data.
- The number columns have an automatic bar graph glyph on them that shows proportions when clicked.
- The number columns are automatically totally.
- The Transactions column is turned off because I said “Dump(2)”, which only dumps to the 2nd level. By default it would drill down further, e.g.
Bringing in Excel
To bring my Excel data into LINQPad, which supports LINQ to SQL, EF and OData natively but not Excel, I have to right-click on the design surface, choose query properties and tell it about where the source code and namespace is that defines the Excel LINQ Query Provider:
Impressions
The thing that makes this app really work for me is the REPL nature. It’s very immediate and I can see where my money is going with very little ceremony. It’s really the intelligence of the Dump command that keeps me from moving this app to WPF. Dump gives me the view I need to understand where my money goes and it gives me the programming surface to slice/dice the data the way I want to. I have no control out of the box in WPF that’s even close to as useful.
However, Even though I could extend LINQPad myself, there is no integrated support for Excel or CSV files. Further, for some stupid reason, I have to load the files into a running instance of Excel for them to load in LINQPad, which pisses me off because the error messages are ridiculous. Also, there is no intrinsic support for multiple data sources; instead I need to build that myself.
Further, I had one case where I couldn’t figure out an error (it was that I forgot to load the data into Excel) and had to have a real debugger, which LINQPad didn’t have. The good news was that I was able to copy/paste my code into a console application and debug it, but the bad news was that I really missed the Dump command when I was running inside Visual Studio.
Where Are We?
I really love LINQPad. In fact, I find myself wanting the same functionality for other uses, e.g. SQL (real SQL), JavaScript and as a shell. It’s the interactive data access that makes it for me — munge some data, look at it, repeat. It doesn’t quite do everything I want, though — where’s the full-featured, all-data, Swiss army knife for data?
December 11, 2010
Tools
October 27, 2010
Spout
At Microsoft, there’s this passive-aggressive cultural thing called a 
September 27, 2010
Tools
August 8, 2010
Spout
