Hacker News Reader

Comment by anakic | Hacker News Reader

A while ago, as a hobby and later a business, I made an Excel plugin for using SQL in Excel: https://www.querystorm.com

I've now added the capability to use C# in Excel, using Roslyn. Sort of like Linqpad in Excel. This lets it work with Excel tables, fiddle with formatting, and automate stuff.

Some of the practical applications:

   - processing data in Excel (LINQ)

   - getting data from various sources into Excel (REST, databases, files, active directory, whatever)

   - building interactive dashboards

   - building prototypes applications (you write logic in C# and use Excel as the UI and data storage)

Here's the introduction: https://querystorm.com/documentation.html

There's also a short video: https://vimeo.com/242216594 for a brief intro.

I charge for the plugin but it's freemium. If you don't want intellisense, error squigglies and so on, you don't need a license.

So... what do you think?

View on HN

It is insane to me that Microsoft hasn't already provided this. I get that you can't replace VBA entirely, or phase it out, without breaking a Lot of Things, and putting a lot of VBA programmers out of work. But every aspect of this being native to Excel would make macro work and data programming easier for a huge segment of the business world.

In other words, I like what you're doing and will probably buy a license. But... I don't see a license option that looks like it lets me test it out in a commercial environment without paying first. I might just be missing something there though. I'd love to test it out and be in compliance with your licensing.

Thanks for the comment! Yeah, VBA in Excel is pretty outdated in many ways, and Excel isn't going anywhere any time soon. I missed having SQL in Excel originally so that's why I started the plugin. I added C# just now, but I really like how C# and Linq fit into Excel, I just love playing around with it.

About licensing: you can get a free trial license by clicking "Try Querystorm" on the QueryStorm website. The trial lasts 14 days, but if you need a longer trial just send me an email (antonio at querystorm.com) and I'll send you an extended trial license.

If you have any suggestions or questions while you're checking it out, let me know.

The problem is interoperability with older versions of Excel when that new one is released too...

You actually can do it, it's just hidden and sort of a pain in the ass. You can insert a SQL-linked datatable that can pull parameters from your spreadsheet, if the cell changes the query updates.

I love it and I hate it at the same time. You would not believe how many people abuse Excel to solve every issue. Entire companies run solely on Excel turned into SAP :-)

QueryStorm: optimizing the way we abuse Excel!:) Well, jokes aside, C# and .NET are a better tool, so at least its easier to make a good solution in Excel. I think Excel is an excellent platform for developer tools. E.g. if you're making a small application or a prototype, you just write code in C# and you have Excel to take care of data storage, data visualization and user input. With formulas and graphs you also get data-binding for free from Excel. Spend 1% of the time for 30% of the functionality you'd need from a proper app.

Congrats! This is fantastic. Now can you do the same with Python please? :) But seriously, great work, including the docs/videos.

Well thanks a lot:) I'd love to support Python, but for now there's not enough manpower to go into it. Glad you like the docs and videos!

I really loved the introduction videos you posted. Great showing of different features in your product - wishing you the best with this project

Thanks, I really appreciate that! Glad the hours I spent preparing the videos and docs didn't go to waste:)

This is absolutely awesome! On a related note, I'm constantly astounded by people who use excel all day but have no interest in getting better at using Excel. I've tried explaining how to use tables and why writing readable formulas is important but they just stare back with a bemused look.

Cheers! Yep, most people can't be bothered to make their own lives easier. The algorithm is: if it involves anything unknown - bail.

Maybe they don't want a system they cannot audit or debug.

Or maybe their data acquisition system is nuts, but maybe it expanded a job for someone who would otherwise be out of one.

I have encountered situations like these and my suggestions... well, not wrong, but not a good fit for that department.

It looks quite amazing. Is there any way to provide som sort of log in form, if data is provided by a protected source?

Of course, the user could just enter credentials in cells, but if I want something more secure. Is there any way to integrate an OAuth 2 log in form or similar?

Yea, you could do it. In the simplest case, you could build a login form (e.g. winforms) in code in your script and use it to get the username and password from the user.

In a more serious use case, you could build your login form and all the login logic in a separate dll, and just call that dll from your scripts.

If you'd like to try this, let me know via email (antonio at querystorm.com), I can help or at least point you in the right direction.

Thanks! Will do.

How about an F# port of this? Aside from it being a very handy language for ETL type tasks, I suspect your average excel power user who otherwise does not program could more easily wrap his head around the functional paradigm.

It's entirely possible. I'll have to see what kind of support Roslyn has for F#. If the support is there, it would be extremely easy to support F#. I'll look into this, thanks for the tip!

For an example for F# perhaps take a look at:

http://fsprojects.github.io/ExcelProvider/

Reply on news.ycombinator.com