Show HN: QueryStorm – A query IDE inside Excel | Hacker News Reader

Show HN: QueryStorm – A query IDE inside Excel

querystorm.com

130 pointsanakic64 comments

I've posted about this about six months ago, the original post (https://news.ycombinator.com/item?id=11583488) was fairly well received, and I thought I'd follow up.

Back then the plugin was called ThingieQuery, which turns out not to be a great name for a product aimed at enterprise customers. Since then, I've been quite busy, I've incorporated, rebranded, sold (licenses), polished, and other verbs as well. I've also adjusted the pricing and switched to a freemium model.

Here's the free vs. paid breakdown:

- Free stuff: basic IDE functionality, full SQL support for tables in Excel, query automation, non-commercial use

- Paid stuff: (in addition to all the free stuff) connectivity to external databases, advanced IDE functionality (autocomplete, error squigglies, etc.), commercial use

The free feature set is pretty useful on its own. Offering it makes sense from a business perspective - to drive adoption, but also from a personal standpoint, since it's a product I'm proud of and would like as many people to make use of it as possible.

The paid licenses come in two flavors:

1. Professional

- for people paying out of pocket

- can activate 2 machines at the same time (e.g. work/home)

- licenses can be transferred ad-hoc to other machines with a 1-min procedure (e.g. when working on a client's computer)

2. Enterprise

- a per-seat license for purchase by companies

- includes support

- can also be transferred from machine to machine

The license types were in large part influenced by the feedback I got in the original post.

I should note that I'm running a December discount promotion; you can use the following key to get a cool 40% discount on a Professional license: RZHDNDT4

In any case, please do take a look and give it a try. The trial licenses (for the premium features) have been reset since last post, so you can use a new one even if you've used a trial before.

Feedback, customers, and free users are most welcome and appreciated:)

Hi, this is great. I'd gladly pay $99 for having such a convenient helper. However, I'm sad to see it's a temporary license. This is not SaaS, but priced like one.

Can't you make it $X for the product + 1 year of updates?

I understand that you need sustainability but you can seek for that on enterprise support deals and by convincing users to renew their license after a year to get a newer version.

Hi patates. I get your point. As a user, I'd want that too. I had previously priced the professional license at $149 as a one-off price, but then noticed companies were buying those instead of the enterprise ones, and getting unlimited licenses. I'd definitely like to keep the subscription model for enterprise licenses, but would not mind having the private ones be a one-off price. I'll have to think about that. If I do that at some point, I'll change the existing licenses to permanent, to not be a jerk to existing customers:)

JetBrains which creates IntelliJ etc is nice this way, they give you a permanent license to the last version released while you were still a customer (provided you are a customer for more than x months first.)

I find this both smart and nice: you can pay monthly and still get a permanent license so you won't be trapped.

Full disclosure: I use Netbeans which is free from the start and which I like better anyway, I'm just saying Jetbrains seems to be a nice company ;-)

Yeah, something like that might be the way to do it. Cut off old licenses from new versions but leave it functioning indefinitely with old versions. Offer a paid upgrade option. I think I like that option.

Always seemed like a fair system to me as well. You're paying for a year of updates, then after that it'll continue to work and receive security-critical updates, but you won't get the new features. Of course, it's possible to totally plan features out over the years to encourage people to upgrade, or e.g. Hold back something many have wanted in order to release it with the next big version where people would get cut off, but alas.

I would suggest leaving the pricing as is.

We know the climbing Everest reality of building software for money. It's an incredible amount of work and a long shot to keep going for any length of time.

Subscription is really necessary to keep great releases new releases coming steadily and to deliver quality support.

I can see the reasoning of making the license permanent without future support and updates, but I still don't begrudge anyone for not doing that.

I'm a developer and not out to try and push the rest of the industry down to the 99 cent app store model.

With the coupon code in this thread, I was willing to go in on it, because it's comparable to what I pay for SSMS Tools. It's really really handy to be able to query an Excel table; a raw query window in SSMS is just about my favorite tool, but you can't beat being able to just get your hands right on the data like in Excel.

Ideally though? I would really like to have a one-time purchase option. I buy this for myself, though I work in a company, because this is a tool I particularly find useful. In a year it's going to just stop working, and I won't remember why, because I won't remember paying a license for it. Then I'll have to look up the email and do all the nonsense to figure out what my login is and buy it again. And that just sucks. I've dropped a lot of services because I just don't need them enough for that. But I'm hoping this works into my flow well enough.

Thanks for your purchase!:) I'm really glad you like it.

One thing to note - since the db engine is running in the same process as Excel it can interact with it. There are some nice things it can do because of this, for instance, SQL-based conditional formatting (e.g. there's a SetFontColor function), calling VBA functions from SQL and a bunch more.

There are also some other quite cool functions, e.g. replacex and regex_split, levdist, eval and others. The QuickQueries dialog has some built-in queries to check out if you're interested in what these can do.

As for permanent licenses, I understand the concern. If I do end up going offering permanent licenses for professional licenses, I'll either upgrade the existing licenses or offer a paid upgrade to the permanent license to cover any price difference.

I'll be honest, I didn't read the full feature list. I looked at some of the quick queries and was really happy to see some of them.

Yeah, the amount of intellectual violence I'll be able to perpetrate against my data is just staggering. Regex in Excel has always be a bit of a slog, but to do it in SQL in Excel? Gosh. That's almost worth the price of adminssion right there.

(Ever try to conditionally refactor a screwy OPC itempath based on an arbitrary spec and bad naming conventions in a cobbled together dump from an overworked customer engineer? Swiss army chainsaws are the only elegant solution there.)

Exactly, you can really go to town on your data, get nasty with it:)

I love regex myself, I feel like a ninja using them:) The replacex function was one of the first ones I implemented. Regex_split is the latest one and I love it (the single post on the QueryStorm blog is about that one).

If you're .NET-savvy, check out the "eval" function, you can implement a function as a C#-like lambda expression.

I did a poor job explaining what the plugin can do, QuickQueries is a step in that direction, but I only prepared a few. Plus they need short videos or at least blog posts to go along with them.

I have to agree, that the SaaS pricing model is a something that kills this for my company. I have no problem with you doing a phone home to verify installation, etc...

Until this pricing model is changed, I will have to pass or wait for an alternative.

It's a shame too, because this seems like a great product.

This looks like something I would like to build one day :)

What I woul like to ask is, how do you find your customers? Especially the enterprise ones?

Because anytime I barely finish something I might be thinking about selling to people (even though I didn' manage to create anything as polished and useful as QueryStorm :) I kind-of freeze at the step "but who would buy this and how would they find about it?"

Often I read that you first need to build an audience, with blog or mailing list, before you start selling something, but it looks like you just built a really cool thing first :)

I had the same problem. Transitioning from having built the thing to asking people to pay for it felt quite awkward at first. I feel a lot less awkward about it now that I do have paying customers who are happy with it and use it daily. It feels great, when someone who isn't legally obliged to, pays you for something you built because it makes their work easier.

>> how do you find your customers? Especially the enterprise ones?

Haphazardly and slowly thus far:) A couple of enterprise customers actually found me, but my own reach into the enterprise world is not really substantial. Some of my customers are consultancies who work with banks and such so I started talking to a few of them about partnership. It's not something I can completely outsource, but partners could certainly help a lot. We'll see how it goes.

Building an audience is a good idea, although it's easier to do if you have something to show people, I think. I'm not a businessy person (maybe I become), I just enjoyed building it, so that's what I spent time on. That's not to say that I wasn't hoping to make a business out of it, I certainly was, from very early on:)

Maybe we should have a marketplace to connect HN projects with customers who want software like this.

Well, there's producthunt, betapage etc.

I just tried this out and I like it. One thing I noticed was that the alert dialog boxes (eg. do you want to save query) all still have the ThingieQuery name on them - FYI.

Nice catch!

How's the business end working out for you right now? After 6 months. This is ycombinator, we're a bunch of entrepreneurs. We want to know these things.

It's going slowly. For now, I make enough to cover my expenses, but I earned more as a contractor working for a corp. Some days, though, I earn way more from QueryStorm, I just have to make those days more common.

People seem to like the product well enough, but I'm kinda stumbling my way around marketing and sales. I think it's going to do well, though, I'm getting better at it, and I'm also likely getting reseller partners soon.

I'm also applying for an EU grant which would allow me to hire some sales/marketing people. That would be great since right now it's difficult to focus on specific things (e.g. SEO, Adwords etc...), mostly it's throwing stuff against the wall to see what sticks.

I liked ThingieQuery! But I understand.

Still an amazing little product!

Thanks, your comment made me smile:)

Excellent new name and your pricing plans sound about right. If I may ask, how are you managing the licensing?

Absolutely. I actually built a reusable infrastructure of my own. It was technically very cool to work on, and at the time I still hadn't yet made the mental shift of buying services that weren't my core domain. The infrastructure revolves around keys and licenses. A license allows one user (or machine) to use the product. It's a digitally signed file that gets downloaded to the client PC and specifies the product, max version, issue/expiry date etc... A license is created by my server when a valid key is entered into the licensing dialog. The key knows what product is for and what kind of licenses to create. I named the service "Keystodian", the website is up but isn't pretty. The infrastructure underneath works nicely, though. I plan to offer it as a SaaS product at some point, though there is a bit of competition there.

Thank you - the discount makes the pricing sane for a weak Australian dollar.

Thank you for buying it!!

Just a heads up, your "buy" button 404s https://www.querystorm.com/payment/buy

Also, remember your first post. Love the new name! Great product too, good luck out there! (Don't think you'll need it).

Thanks for the heads up (fixed it) and the kind words!

Now it's a 500.

To clarify, he didn't fix the page I mentioned, he just changed the link to point to the new place :)

Seems to work fine for me. Hopefully it was just a glitch.

Is there anyone doing anything like this out in open-source land?

Is there anywhere I should go to find open-source resources for Excel?

It seems like most Excel stuff that is easy to find is distributed similarly to this tool. Managing macros in source control is annoying!

govert's excellent ExcelDNA [1] enables one to build XLL Addins in .Net. Many use it for crafting Excel extensions, including BlockSpring. I used it for SSAddin [2] to build cron jobs as well as tiingo and quandl downloads in Excel.

[1] https://github.com/Excel-DNA [2] https://github.com/SpreadServe/SSAddin

About source control in Excel, there's a London based company offering a product along those lines called Pathio (https://www.pathio.com/). I know it's not open source, but I'm mentioning it just in case you find it interesting. I actually had a pleasant chat with the founder of Pathio a few months ago, so figured I'd plug the product:)

What I think is missing in term of data analysis in Excel, is a pivot table that can work on a relational dataset in a SQL server, without downloading the whole table as a join as a first step.

Use case: the data is too large to be downloaded and held in memory on a client computer. What you really want to happen is for the aggregation to happen on the SQL server. So that the pivot table would generate SQL code dynamically for the SQL server to do the equivalent of what the pivot table would do with in-memory data, and would only download and display the result.

SQL is useful to developpers, but you can't expect business users to write complex queries.

>> a pivot table that can work on a relational dataset in a SQL server, without downloading the whole table as a join as a first step.

You can do that with PowerPivot + SQL Server Analysis Services, no? Requires SQL Server and SSAS licenses, though.

>> SQL is useful to developers, but you can't expect business users to write complex queries.

True. I remember reading/hearing somewhere that it was originally designed to be used by business people as well, but it didn't really turn out that way.

I wanted to add that QueryStorm isn't just a pretty IDE:) It's a runtime as well. IT people can use it to prepare interactive reports that they can then share with business people.

E.g. you change the value of a cell in Excel, a query is triggered in the background that uses that cell's value as a parameter, and updates an Excel table with the results. The query can use Excel tables and cells as well as outside databases. The user is just interacting with the workbook, without typing any SQL.

I also added a QuickQueries dialog that can generate/run some useful queries. You can use it to do stuff without knowing SQL e.g. find outliers (people whose salary is > +2 stdevs for their jobtitle). The idea is to make it useful to non-techy people and to give techy people ideas of what it can do and a starting point for more complex queries.

Basic SQL is pretty easy to pick up, especially if you're motivated to get something done. You can get a lot done knowing how to work simple JOINs, WHERE clauses, and some aggregate functions.

Yeah, if you can understand vlookup, you can certainly pick up sql joins fairly easily. You can do quite a lot even with basic SQL skills.

Reaching business people from a marketing perspective and talking to them about SQL is a whole different thing, though.

Yeah but I found myself in that exact position. I provided data to a bunch of business users who don't understand SQL but understand pivot tables extensively.

I have the choice between either spending a few days educating them on how to do SQL, or to give them data in a way that they can use the tools they are already familiar with.

I was too busy to do the former.

The other thing is that SQL sucks at creating pivot tables. It is good when you only want to aggregate in one direction. When you want to aggregat in multiple directions (column A by row, column B by row) the syntax becomes tedious to do by hand.

Excel has pivot table feature but not sure if it works with SQL database.

For analysts or business users you need just drag and drop feature but it is always good to know what SQL is generated by the actions like you can see that with http://infocaptor.com/

http://infocaptor.com/img_assets/finance_data_viz1.gif

It works with an excel database but it requires you to select a single table through a join, downloads all data to memory, and then does the pivotting in-memory. Impractical for a large dataset.

Awesome! I work in Excel on a shared Windows server VM. Is it possible to install an add-in like this for one user, or will everyone using Excel on this VM see the add-in?

Ideally, I'd buy a license for myself right now and start using it if I could install the add-in only for myself without needing an admin user to install it.

Glad you like it!:) It's a per-user installer, so only you would see it. It shouldn't require admin privileges unless a prerequisite is missing.

It requires .NET 4.5 and vstor (visual studio tools for office runtime).

- .NET 4.5 comes out of the box with Win8 or higher.

- vstor comes out of the box with Office2013 or higher.

QueryStorm's installer checks if the two prerequisites are present, and if not, downloads them from Microsoft and installs them automatically. It will prompt for admin rights only if one of them is not present and needs to be installed.

Is it popping an admin prompt for you?

I'm assuming you're using the traditional excel plugin model (written in c++ or .net) and not their new "Office Apps" (formerly add-ins) that works on both excel native and in Office365 web versions of excel?

Yeah, the .NET one - VSTO, visual studio tools for office. Haven't played around much with the web one. Most things QueryStorm does wouldn't be possible in the office apps model, e.g. hosting SQLite. It's too bad because it limits QueryStorm to PC desktop only.

> "Most things QueryStorm does wouldn't be possible in the office apps model, e.g. hosting SQLite."

Odd, what problems would you have hosting SQLite with an Office App? I've got a solution that could work if you're interested, it's a bit of a hack but I've done a proof of concept before that shows it works... Essentially you can run your own local web server as a service, and have the Office App make use of this service. You could even get the web server to start when Excel starts.

Honestly I haven't tried Office Apps, it just seemd like it would be tricky and I didn't have time to investigate. I can see how the local web server hack might work, though. Would it work in the browser and on the Mac?

> "Would it work in the browser"

Depends on how you host the app. If it's hosted locally, then no.

>"and on the Mac"

Maybe. I'm nor that familiar with the Office for Mac features. I did try to search online to see whether Office for Mac supported Office Apps, but it's not the easiest thing to search for.

I am proud I was one of the firsts to upvote and comment about ThingieQuery on HN, 604 days ago (if I remember well I was the first one, and my comment reflects that).

I liked the other name better, but I understand the reasons for changing it. I am pretty happy it grew into a sustainable and profitable product.

I'm curious about the use-cases this plugin has in enterprise companies. I've never worked in big companies that used Excel or whatever. What is the flow? Which kind of companies use this?

I worked at a logistics company once where Excel was one of the main programs in use. It's used to pass around data, e.g., lists of shipments. The shipment programs and business intelligence programs give you Excel files when you press export. People then filter out the unneeded information, maybe highlight stuff, maybe make a PivotTable, add extra information from other Excel files or other sources, and submit it as a report. If you want to automate this, and you (the dev) cannot access the servers with the raw data either, you would normally write tedious VBA code. With SQL, it may get a bit more comfortable. (You can use SQL to query Excel sheets in VBA, but the boilerplate code for that is tedious too.) I'm not sure if my previous company would be a great fit, because the automated reports are usually run by the users themselves. Per-seat licenses would be very expensive.

Hey, I remember your username from back then, thanks for the support!! Yeah, that was the first HN post I did.

About use-cases: techy people use if for hacking away at data in Excel and import/export. Enterprises also seem to want the automation part. The main scenario there is to have IT folks prepare queries, embed them into the workbook and set up triggers. Business users or clients then just interact with the workbook, without caring about the SQL queries that are running in the background populating data. Nice for interactive reports, much faster than building web applications for the purpose. That said, I do need to start doing surveys as this are just my conclusions based on email exchanges with users.

It's great to know I don't have to know all the dirty details behind enterprises to develop a useful and profitable product!

I forgot to paste the link to prove my bragging point: https://news.ycombinator.com/item?id=9364598

Given that MS Office is priced at $99 per year, your pricing seems very high.

Is anyone having installation issues? I'm showing strange things in logs. I can see downloads, but my licensing service does not seem to be issuing the free licenses.

Looks great but I would guess all this functionality is already built into excel somewhere. You just have to be ninja enough to find it.

Well, some things you certainly could, but this really opens up many more possibilities than is obvious at first glance. Another big thing is convenience, which is along the lines of what you said. Single click to open query editor, full autocomplete, star expansion, etc., easy 2-way communication between a db and Excel (instead of the clunky import/export procedures).

There are a ton of areas where this offers benefits, in larger or smaller ways. It can solve problems people don't even recognize.

I guess that's one problem you have to worry about now. Microsoft is bound to incorporate something like this into Excel sooner or later. :(

Better yet, Microsoft might offer to buy them out.

throwback https://www.youtube.com/watch?v=H27rfr59RiE

Excel has Get & Transform, formerly known as Power Query, which can do similar things... if a custom query language [1] instead of SQL doesn't scare you away. I have used it a few times, it can definitely be useful.

[1]: https://msdn.microsoft.com/en-us/library/mt270258.aspx

You can write SQL queries in Excel (in numerous different parts of the application), but no built in SQL autocomplete in Excel AFAIK.

anybody remembers the tiny almost hidden relational editor built into excel ?

I bought this last time it was posted here, back when it was ThingieQuery. It's been super useful to me. Definitely recommend trying it out, it's basically a SQL IDE within Excel.

Some of the things I've used it for:

- Poking around random SQLite databases

- Importing oil&gas production data from SQL Server, and running calculations in Excel

- Running SQL queries on large Excel tables created as a result of some screen-scraping

Reply on news.ycombinator.com