4 ms·

I'm a professinal C# dev. When I need to do any one off analysis of data or a running system, I just spin up linqpad, write some C# to grab my data, put it thr

I'm a professinal C# dev. When I need to do any one off analysis of data or a running system, I just spin up linqpad, write some C# to grab my data, put it through a functional transformation pipeline with LINQ, then, optionally, graph the results using some WPF graphing components. I hit F5, and my results appear in the window below as either a table of data or a graph. I don't even have excel installed on my windows desktops anymore. A 50 dollar tool from an upstart programmer is better for me than excel.

cm2187 9y ago
Yeah but data analysis is only one of many usages for Excel. Let me give you a practical example I ran last week. I want to construct a portfolio of loans starting with an existing portfolio, such that it follow a certain total balance and maturity profile through time, by replenishing it every month with new loans of various maturity (I am solving for the individual notionals of these new loans). So inputs are 1. the existing portfolio, hundreds of entries with various parameters: start, end, notional, etc 2. the target maturity profile at every point in time 3. the target balance of the portfolio at every point in time A simple LINQ query won't do. For the example above, you want to model it in C#, but the inputs are lots of tables. In that case I find the optimal way is for the script to accept spreadsheets as input files (wrote a "xlsx (de)serialiser") and output files. Effectively Excel becomes the UI of the script. Nothing beats the Excel UX in giving you the ability to build a table, change or copy/paste data in bulk, etc.
- rjbwork 9y ago
  Personally I'd just throw it into a number of SQL Lite table and run Linq to it via EF. Or just write SQL against it. Pretty much just text files. If it were too big (probably not if Excel can handle it) I could also just load up the data into memory via a TSV/CSV deserializer, of which there are a few for .NET. If it comes as excel from the source, excel extraction is a piece of cake these days (the codebase I work on takes excel as raw input for data feeds). As for nothing else letting you build/change/copy/paste data into a table, Sublime Text Advanced CSV package is fantastic for editing tabular data, and Sublime Text has no problem handling files many gigabytes in size.

cm2187 9y ago

Yeah but data analysis is only one of many usages for Excel. Let me give you a practical example I ran last week. I want to construct a portfolio of loans starting with an existing portfolio, such that it follow a certain total balance and maturity profile through time, by replenishing it every month with new loans of various maturity (I am solving for the individual notionals of these new loans). So inputs are 1. the existing portfolio, hundreds of entries with various parameters: start, end, notional, etc 2. the target maturity profile at every point in time 3. the target balance of the portfolio at every point in time A simple LINQ query won't do. For the example above, you want to model it in C#, but the inputs are lots of tables. In that case I find the optimal way is for the script to accept spreadsheets as input files (wrote a "xlsx (de)serialiser") and output files. Effectively Excel becomes the UI of the script. Nothing beats the Excel UX in giving you the ability to build a table, change or copy/paste data in bulk, etc.

rjbwork 9y ago
Personally I'd just throw it into a number of SQL Lite table and run Linq to it via EF. Or just write SQL against it. Pretty much just text files. If it were too big (probably not if Excel can handle it) I could also just load up the data into memory via a TSV/CSV deserializer, of which there are a few for .NET. If it comes as excel from the source, excel extraction is a piece of cake these days (the codebase I work on takes excel as raw input for data feeds). As for nothing else letting you build/change/copy/paste data into a table, Sublime Text Advanced CSV package is fantastic for editing tabular data, and Sublime Text has no problem handling files many gigabytes in size.