4 ms·

> The issue is not how to get rid of Excel, it's how do we make a better spreadsheet... Or how do we better support custom tools integrating with Excel. Excel

> The issue is not how to get rid of Excel, it's how do we make a better spreadsheet... Or how do we better support custom tools integrating with Excel. Excel has a number of extensibility models... maybe too many; * VBA, * XLL add-ins (which is a C++ API, but check out the excellent Excel-DNA [1] which is a C# wrapper around this), * Visual Studio Tools for Office, which doesn't support the ability to implement your own custom functions (e.g. =MyAddIn.GetStockPrice() unlike the previous two options, and * The more recent 'Apps for Office' extensibility model across the whole Office suite, which is basically an embedded web browser instance in your Office app which gets a Javascript API for interacting with your Office document. This is also getting custom functions support in Excel [2] At the energy consultancy I work for we have built a suite of Excel add-ins that allow analysts to get the data that they want easily into their sheets, but don't impose any (unreasonable) restrictions on how they work with the data. [1] https://excel-dna.net/ https://excel-dna.net/ [2] https://dev.office.com/docs/add-ins/excel/custom-functions-overview https://dev.office.com/docs/add-ins/excel/custom-functions-o...

d--b 9y ago
Better addins only means you get to do more stuff with Excel. Instead of doing one model, you may now be able to do a time series of models, or calculate The VaR (Value at Risk). There is no end to what people can ask from data. The only limitations are technological. And whatever extension you may have, your users will always push Excel to its limits.
- cm2187 9y ago
  I am a typical excel power user, and have started to increasingly do work outside of Excel (C# mostly). I find however that Excel is still a formidable GUI, particularly for tabular inputs. One middle ground is libraries like SpreadsheetGear that give you a UI that has an almost perfect excel look and feel while giving you more control. You don't need to integrate your tool in Excel, you can integrate a quasi-Excel in your (.net) tool.
  - rjbwork 9y ago
    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.

d--b 9y ago

Better addins only means you get to do more stuff with Excel. Instead of doing one model, you may now be able to do a time series of models, or calculate The VaR (Value at Risk). There is no end to what people can ask from data. The only limitations are technological. And whatever extension you may have, your users will always push Excel to its limits.

cm2187 9y ago
I am a typical excel power user, and have started to increasingly do work outside of Excel (C# mostly). I find however that Excel is still a formidable GUI, particularly for tabular inputs. One middle ground is libraries like SpreadsheetGear that give you a UI that has an almost perfect excel look and feel while giving you more control. You don't need to integrate your tool in Excel, you can integrate a quasi-Excel in your (.net) tool.
- rjbwork 9y ago
  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.