Skip to content

How to access your QuickBooks Online data via API from the command line

As is so often with this blog, I’m documenting something that took me a long time to figure out.

The problem: the company I work for has data in QuickBooks Online (QBO) that we’d like to distribute elsewhere in our organization.

The answer: QBO has an API. Yahoo! They have several different SDKs (for .NET, Java and PHP) to make access even easier. (The APIs and surrounding docs are called the Intuit Partner Platform, or IPP.)

However…

The issue is that requests to the QBO API must be authenticated with OAuth (I believe it is OAuth 1.0a). And the entire Intuit Partner Platform documentation is focused on needs of developers who are building SaaS applications that consume or augment QBO data and are accessed via the QBO webapp. Which means there’s a heavy focus on web applications and OAuth flows via web applications.

But all I wanted was a command line client that could use the API’s query interface.

I was stymied by OAuth. In particular, I couldn’t find a way to get the accessToken and accessTokenSecret. I tried a number of different tacks (I beat my head against this for the better part of day).

But I just couldn’t find a way to generate the needed tokens, with either the PHP or Java SDK clients (most of the links in this post are for the Java client, because that’s more mature–the PHP client doesn’t support JSON output or have reference documentation).

Desperate, I turned to the IPP forums, which were full of advanced questions. I did stumble on this gem: “Simple way to integrate with our Quickbooks Account”, which took me to the OAuth Playground for IPP Developers. And, voila, if you follow the steps in the playground (I used IE because FireFox failed), you will end up with a valid accessToken and accessTokenSecret.

So, with that sad story told, here’s exactly how you can take access your own QBO data via the command line (I only cover a trial account here, but believe the process is much the same with a paid account):

  1. Start your IE browser (I’m using IE 10 on windows 8)
  2. Go to https://developer.intuit.com/us
  3. Sign up (click the ‘join’ link), click ‘remember me’
  4. Go to your email and find the verify link that was sent to you. Paste it into your IE browser.
  5. Sign in again
  6. Click on ‘My Apps’
  7. Click on ‘Create New App’, then ‘QuickBooks API’
  8. Fill out the name of the app, and the other required items. You can change these all later (I think). I know you can change the URLs later.
  9. Select the level of data access you need. Since this is a test app, you can select ‘All Accounting’
  10. Click ‘Save’
  11. Open up another tab in IE and go to the QuickBooks Online site (We are just adding some dummy data here, so if you have an account, you can skip this.)
  12. Click on ‘Free Trial’
  13. Click on ‘QuickBooks Online Plus’
  14. Click on ‘Already have an Intuit user ID’
  15. Fill out the username and password you used on when you signed up for your developer account.
  16. Ignore the upsell, if any
  17. Click the customers tab
  18. Click on the ‘new customer’ button
  19. Enter a first name and last name then press save
  20. Open a new tab and go to the API Console
  21. Choose the company that you want to access, and note the number next to that name. That is the company ID or the Realm ID.
  22. Open a new tab and go to the OAuth playground
  23. Go back to the developer.intuit.com tab
  24. Grab your app token (looks like b3197323bda36333b4b5fb17774440fe34d6)
  25. Go to the OAuth playground tab and put your app token in the proper field (called ‘App Token’). You’ll also want to have that later, so note it now.
  26. Click ‘Get Key and Secret using App Token’
  27. Note the consumer key and consumer secret, you’ll need them later.
  28. Click ‘Get Request Token Using Key and Secret’
  29. Click ‘Authorize Request Token’
  30. You should see a message like ‘testapp3 would like to access your Intuit company data’
  31. Click ‘Authorize’
  32. You should see a message like ‘You are securely connected to testapp3’
  33. Click ‘Return to TestApp3’
  34. Scroll down to the bottom, and you should see entries in the ‘Access Token’ and ‘Access Token Secret’ fields. Copy those, as you’ll need them later as well.
  35. Go to the SDKs page of developer.intuit.com
  36. Pick your language of choice, and follow the installation instructions.
  37. Follow the instructions in the ‘Data Service APIs’ section about setting up your environment. For Java, you’ll need to pull a few jar files into your classpath. Here’s my list of jar files in my Eclipse build path: ipp-java-qbapihelper-1.2.0-jar-with-dependencies.jar, ipp-v3-java-devkit-2.0.3-jar-with-dependencies.jar
  38. Write and run a class (like the one below) that runs a query, plugging in the six variables the values you captured above.
import static com.intuit.ipp.query.GenerateQuery.$;
import static com.intuit.ipp.query.GenerateQuery.select;

import com.intuit.ipp.core.Context;
import com.intuit.ipp.core.ServiceType;
import com.intuit.ipp.data.Customer;
import com.intuit.ipp.exception.FMSException;
import com.intuit.ipp.query.GenerateQuery;
import com.intuit.ipp.security.OAuthAuthorizer;
import com.intuit.ipp.services.DataService;
import com.intuit.ipp.services.QueryResult;

public class TestQBO {

	public static void main(String[] args) throws FMSException {
		String consumerKey = "...";
		String consumerSecret = "...";
		String accessToken = "...";
		String accessTokenSecret = "...";
		String appToken = "...";
		String companyId = "...";
		
		OAuthAuthorizer oauth = new OAuthAuthorizer(consumerKey, consumerSecret, accessToken, accessTokenSecret);           
		
		Context context = new Context(oauth, appToken, ServiceType.QBO, companyId);
		
		DataService service = new DataService(context);
		
		Customer customer = GenerateQuery.createQueryEntity(Customer.class);
		
		String query = select($(customer.getId()), $(customer.getGivenName())).generate();
		
		QueryResult queryResult = service.executeQuery(query);
	
		System.out.println("from query: "+((Customer)queryResult.getEntities().get(0)).getGivenName());      
	}
}

This code gets the first name and id of the first customer in your database, and prints it to stdout. Obviously just a starting point.

I am also not sure how long the accessToken and accessTokenSecret are good for, but this will actually give you command line access to your QBO data.

(Of course, I could have just used zapier, but that has its own limitations (limited ability to query data in an adhoc manner being the primary one).

Easyrec: a recommendation engine worth looking at

I love recommendation engines.  These are the software that Amazon has everywhere showing “users who bought this also bought” recommendations.

I love them because they are an easy way to leverage the wisdom of the crowd to help users.  They also get better the more data you feed into them, so once you set one up, it just makes your site better and better.

For a while, I’ve wanted to explore mahout as a recommendation engine solution, but felt intimidated by how much work integration would be.  Luckily, I did a bit of searching and turned up this stackoverflow question about java recommendation engines.

Looking at some of the alternatives, I dug up easyrec, an open source recommendation engine.  Rather than solving a couple of different machine learning problems like mahout does, easyrec focuses on recommendations.

It also has a javascript API (for both sending information and displaying recommendations) and a demo installation you can use on your site, so it is trivial to integrate into a website to see if it works for you.  I did run into an issue with the demo server, but a post to the forums got it resolved in a few days.

Easyrec has support for generating recommendations for more than one kind of item (so if you want to display different recommendations within specific categories of an ecommerce site, that is possible) and is self hostable in any java container (which is recommended if you are going to use it in any commercial capacity).  You can also build the recommendations off of the following actions: views, rating, or purchase.

You can also customize easyrec with java plugins, though mahout definitely offers far more options for configuruation.

I haven’t noticed any speed changes to my site with the javascript installed, though I’m sure adding some more remote javascript code didn’t speed up page rendering.  I noticed an uptick in time on site after I installed it (small, on the order of 5%).

If you have a set of items that are viewed together, using easyrec can leverage the wisdom of the crowds with not much effort on your part.  It’s not as powerful or configurable as alternatives, but it drop dead simple to get started with.  It’s worth a look.

Google Analytics Goals and Forms on Google Sites

I recently put together a website for my father’s new book about World War II, and choosing Google Sites was a slam dunk.  I choose a neutral theme, set up a few pages, uploaded some images and logged into Go Daddy (where my father had purchased his domain name) and set up DNS.  Easy peasy.

Two issues came up.  One minor and one major.

First, there is no easy way to point the bare domain at google sites using Go Daddy’s tools.  So seesaw1942.com can’t be redirected to www.seesaw1942.com.  Minor bummer, but just make sure all the marketing contains www.seesaw1942.com

I set up a form to capture email addresses for people who wanted to be informed when the book was actually published (it is now!).  I was interested in playing around with PPC to drive people to the website, but wasn’t able to set up a goal in Google Analytics to measure signup success.  (So I didn’t end up setting up PPC.)

I looked around and didn’t find anything that would help with this.  Here’s an article about goals and Google Forms, but the writer’s form lives on their own server, which gives them more latitude than someone working with Google Sites.

Anyone have any idea how to do this?

List of online real estate solutions

I recently was involved with a extensive survey of online solutions for my employer, a real estate brokerage.  Some were open source, some commercial, some inexpensive and some expensive.  Basically, any full solution that might have worked was added to the list.  Some of these are aimed only at real estate agents (as opposed to a brokerage), but others definitely scale up to brokerages.  (I say full solutions, so you won’t see anything on the list like Onboard Informatics or Altos Research.)

I just wanted to list all of them here for posterity and to help anyone else looking for a real estate solution.

On Being Disrupted

This is a tough post to write.  I’m at the tail end of an evaluation process for my company that ended up with us deciding to go with a third party vendor for software which powers key area of our business.  It is augmentation rather than replacement, but still.

It was hard for me, because this particular key functionality was previously provided by custom software that I helped build over years.

I like to build things!  Like most other software developers, I get excited about building stuff.  One of the unmentioned frustrations of many software developers is building something and then seeing it shelved.

However, it was clear from the survey of solutions that there were three choices:

  1. buy something off the shelf
  2. get better as software developers and really, really accelerate our development
  3. have the business be negatively affected by this piece of software

Now, #3 is obviously not a good solution.  #2 is a great solution, but is hard to put into practice, especially in a short time frame with a large code base (though we are trying to use some of the agile methodologies to make our software development more productive).

#1 it is.

Was this a wise choice?  Talk to me at the end of the implementation, but I am hopeful.  We did take several steps to protect ourself (after all outsourcing core business functionality can be deadly), including:

  • a long, laborious evaluation
  • engagement with multiple vendors, and
  • building a set of criteria to help us determine if this outsourcing is meeting our needs

One thing that helped me take this decision a little less personally is to redefine in my mind the value of software to the company.  It’s not the particular implementation of the software that provides the value.

Unlike a software company, my company doesn’t exist to write software (see Five Worlds for more on different types of sofware development).  Instead, software exists to serve the company.  Having something off the shelf provides the similar functionality for much cheaper.  It also allows me and other members of the software team to write software that is unique.

Having been a contractor and having worked for a startup and consultancy, I’m used to being the disruptor.  In this scenario, I was the disrupted (ht, David Skinner).  It’s a humbling place to be, even if I wasn’t disrupted out of a job.

Building A Site With Google Sites

I recently built out a site for a local tax firm, Cahill, O’Kelly and Associates using Google Sites.  I’ve already talked a bit about using Google Sites for your web presence, but I wanted to share a bit more about my experience.

The reasons I chose Google Sites over something more flexible was pretty much based on what I determined was best for the client.

  • the client is not super technical and was wary of a website
  • I wanted the client to be able to update the site so they wouldn’t have to pay me to
  • they were not super picky about design of the site, so were willing to pick from a template for look and feel
  • site was small and functionality desired was limited (brochureware)

All of these added up to doing something that was quick, cheap, low risk, and easy.  If any of these decision points had been different, then Google Sites would have become a less attractive choice.

Good things about Google sites

  • easy to get going
  • easy to integrate with all of googles other services (analytics, webmaster tools, google forms, etc)
  • lots of templates to choose from
  • hosting is free

Things that suck about Google sites

  • the footer links are out of your control
  • some of the templates require photoshop knowledge to customize (background images)
  • no easy way to roll back changes to your site
  • some of the admin UI is clunky
  • scrolling around the look and feel customization page was difficult

Tips for developing on Google Sites

  • make sure your customer fits the profile mentioned above
  • educate your client on the limitations
  • make backup copies of your sites before you do big look and feel changes.  That way you can manually view the differences and roll back if needed
  • get to know google forms, as that is the only way to do interactive forms that I found
  • be prepared to spend some time tweaking the layout
  • be prepared to do some training on how to edit the site

All in all, Google Sites is a great solution for a certain type of client.  Consider adding it to your toolbox if you do site development.

Own your social media–install Storytlr

I guess I’m just not very trusting, because I like to have copies of my data.  I host my own blog, rather than use blogger or wordpress.com.  I host my own email (or at least one of my two main accounts).  I prefer to document interesting things on my blog, rather than a site like Quora or Stack Overflow (though I do have an account on the latter).  Heck, even though I use an open ID provider, my own domain is the master, and I just delegate to myopenid.com.

So, since I recently have been putting a bit more effort into my social media presence (you can find me on twitter here), I looked around to find a backup solution.  I did find one–Storytlr–via this article on backing up your twitter feed.  It apparently used to be a hosted service, but now is open source–code here, install instructions here.  (There’s at least one for pay service too, but then, you don’t really own your data, plus I’m cheap.)

It was pretty trivial to install.  I ran into this issue with Storytlr not recognizing that PDO was installed, but the fix (hacking the install script) worked, and I didn’t run into the Zend error also in that bug post.

I also ran into an issue where I chose an admin password of less than six characters on install.  Storytlr was happy to let me do that, but then wouldn’t let me enter the exact same password when I was logging in for the first time.  To fix this, I had to update the password column in the users table with a new MD5 string, created using this tool.

So, what does Storytlr actually give me?

  • Access to my data: I set up feeds to be polled regularly (requires access to cron) and can export them to CSV whenever I want.  And I keep them as long as I want to.
  • One single point of view of all my social content.
  • Really easy way to add more feeds if I join a new social network.  Here are the sites/networks Storytlr supports right now.

The issues I ran into are:

  • Technical issues, resolved as documented above.
  • No support for facebook.  (Well, there’s this experimental support, announced here, but nothing that is part of the project.)  This is big, given how bad Facebook is with respect to privacy.  I am not sure what my next steps are here.
  • Not wanting others to have access to my lifestream.  This was easily fixed with a Auth directive.

If you are depending on social media sites, have some technical chops, a server to host it on, and want to ensure a historical archive, you should look at Storytlr.

Writing a munin plugin

Recently, we’ve run into some stability issues with our main web application.  It’s a small company, so even though I’m definitely not the ops guy, everyone is pitching in with ideas and suggestions.  One thing that the ops guy did install that has been super useful is munin.  This graphing software lets you monitor, over time, many different aspects of your web application and/or servers.  It has been invaluable in letting us know what the effects of the various changes we’ve made have been.

Questions that munin helps you answer can pretty impressive.  For example, does doubling the amount of memory available to your webapp container help stability?  How can you know unless you’re measuring stability?  What happens if you prohibit certain bots from visiting your website?  When during the day or week is your server running hottest?

We have a watchdog that monitors our main application server, and if it is not responsive, restarts it.  The watchdog also records when the restart occurred.  I decided, as a fun project, to write a plugin for munin that would graph the number of restarts per day, as a high level ‘are we more stable yet’ graph.

Writing a plugin was trivial–it’s a shell script that follows certain output formatting. All I really needed was this HOWTO and this explanation of the types of data sources, though the FAQ is, as per usual, worth a scan.

Munin is by no means perfect (my dream feature would be the ability to annotate graphs at a certain moment in time; ‘this is when we released version 2.1’), but it is a huge hammer in the IT toolbox for understanding current and historic behavior of your application.

Gratitude List

Every so often, I just need to stop and say thanks.

Thanks for my family and parents, providing all kinds of education when they raised me.

Thanks to my wife, for being patient and loving, even when I’m not my best.

Thanks to my past and current clients, colleagues and employers, for giving me amazing opportunities, challenging me and trusting me.

Thanks to the vast number of people who contribute to making the internet a fantastic place (this is a software blog, after all).  Whether you create content or build plumbing, the internet is richer for your contribution (even the guy behind lolcats, but especially stuff like this and this).

Every so often, I get wrapped up in some small drama, but nothing knocks me back on my heels and makes me say ‘Damn, I’m lucky’ like making a gratitude list, even one like this which is incomplete.

Say thanks–it feels good!

Useful Tools: StatsMix makes it easy to build a dashboard

I haven’t been to a BDNT lately, but still get their email announcements.  In August, all the 2010 TechStars folks presented, and were listed in the email.  I took a look at each company, and signed up when the company seemed to be doing something cool.  I always want to capture my preferred username, mooreds!

One that was very interesting to me was StatsMix; I signed up for their beta.  On Nov 1, I got invited to sign up.  Wahoo!

Statsmix lets users build custom dashboards.  I am developing an interest in web analytics (aside: if you are interested in this topic, I highly recommended Web Analytics 2.0, by Avinahsh Kaushik).  I’ve been playing with Piwik, an open source analytics toolkit, but Statsmix offers a slicker solution.

They have made it dead simple to create a custom dashboard for users.  They offer integration with, at this time, 29 services (twitter, mailchimp, youtube, Google Analytics, etc).  I could not find an up to date list of integration services outside of their webapplication!  The best I could find was this list from September.  While the integration interface is slick, the data integration is rudimentary.  For example, they will let you monitor the number of rows in a Google Spreadsheet, but nothing more (like rows in different columns, or the value in a particular cell–would be nice to see them integrate with Google Apps Scripting); you can track the number of likes on Facebook, but not the number of comments.

The real power of StatsMix comes from the ease of integration with your own custom stats.  They offer an API which is accessible via REST.  This means that you can push information from your database to a beautiful looking dashboard with shell scripts and a cron job.  Very cool!  It would be nice to see a plugin for Magento or other ecommerce vendors; I recently had a client, The Game Frame, that would have been a great fit for this type of dashboard, since it aggregates beyond what the ecommerce software provides.

Other cool features:

  • The whole UI is beautiful and farily intuitive.
  • The dashboard supports custom date ranges.
  • They will send you an email of stats every day, and apparently have some kind of limited version you can pass onto clients.  I didn’t play with the email feature at all, though it is extremely useful.

However, all is not perfect.  Some issues with StatsMix include:

  • As mentioned above, the integration with third party services leaves something to be desired.  What they offer is a nice start, but it’d be great to see them create some kind of marketplace where developers could build solutions.  For example, the twitter widget only tracks the number of followers.  From the TWitter API, it appears to be pretty easy to track the number of mentions, which could be a useful metric.
  • It wasn’t clear how to share a dashboard, though that may be an upcoming feature.
  • The terms of use are, as always, pretty punishing.
  • Once you develop a number of custom metrics, you are tied to their platform.  That wouldn’t be so bad, except…
  • They are planning to charge for the service, but give no insight into what to expect.  There is a tab called ‘Billing’ but all it says is: “During our beta, StatsMix is free to use. After the beta, you’ll be able to manage your billing preferences on this page.”  If I was considering using this as part of my business, I would want much more insight into possible costs before I committed much time to custom metric buildouts.  I’m fine with them making money, just want more insight into this key aspect of their web app.

All in all, it is well worth a try.  If you to, let me know by posting a comment.  I have 5 invites to give out.