Importing Transactions From Vagaro To Quickbooks

Attention Conservation Notice: This is a simple post intended for a specialized audience: small salon owners using Vagaro and Quickbooks, who want to be able to track transactions effectively.  It’s really not interesting, at all, for anyone else.

Like a lot of salons, we use Vagaro to manage our scheduling, etc.  Like a lot of salons, we use Quickbooks (Online) to manage our bookkeeping tasks.  I think Vagaro is generally a great product for managing stylists, booking appointments, and so on.  For organizing finances, it’s not so great.  It has some reasonable internal tools and widgets that let you query your data in various specific ways, but for any real analysis, of course you want more powerful tools.  I’ll be writing more someday soon about the analyses of our business I’ve run in R using Vagaro data, but now I want to talk about something a little more prosaic: using Vagaro data to do bookkeeping.

The basic trouble is that while Vagaro stores your transactions and will compute payroll for you, it will not Export those transactions in a manner that Quickbooks can read. Vagaro’s stated intention (for some time now) is to build an inhouse bookkeeping software designed for salons.  Well.  I don’t personally think this will work–salons come in all shapes and sizes, and with many different kinds of needs, and Vagaro is a pretty small company. More importantly, I don’t think that many accountants will accept a product other than Quickbooks–I know ours won’t!  Most importantly, the Vagaro product doesn’t exist.  So if you’re not willing to wait to do your books, you have a problem: how do you get your Vagaro transactions into a form readable by Quickbooks?

I’ve solved this problem in a way that is suitable for our salon (with about 6 stylists, and roughly 150-200 transactions / week), which uses Baystate Consulting’s Transaction Pro Importer.  This tool is not free–in fact, it costs as much as a subscription to Quickbooks, but it has saved us many hours of data entry.  Unfortunately, Vagaro’s default excel files don’t match the expectations of Quickbooks, Transaction Pro Importer, or any balance-based accounting scheme.  So you have to do some rearranging to get it to work. For instance, you want each item (product, tips, and payment) on a separate line, connected by a common reference number, showing you that they are part of the same transaction.  Vagaro puts tips on the same line as purchases, and doesn’t make any connection between items in a single purchase.

If you’re in the same situation we were in, this step-by-step set of instructions may work for you.  The general gist goes like this: you download the file from Vagaro, do a bunch of editing in Excel to get it to look like something Transaction Pro Importer can handle, then import the file that results into Quickbooks Online. Finally, you check to make sure the whole thing came out right.  Start to finish, the whole thing takes about 45 minutes once you get it right.

Update: I’ve written an importer in javascript that simplifies much of the old instructions.  These are substantially revised. The old instructions are still available at the bottom, but we find these much easier to use.

Importing Transactions into Vagaro

Downloading the Transactions From Vagaro

Note: I’m writing these instructions in September, 2013. Vagaro changes it’s menu frequently, so beware and be aware.

  1. In Vagaro, go to Reports -> Transactions List
  2. Select "From" and "To" dates
  3.  From should be one day after the last recorded transactions
  4. To should be the date of the last completed day (don’t use the day you’re in, unless the doors have closed)
  5. Note: Transaction Pro importer online does not allow more than 2 megs or 1000 lines, so it may be better to do this in small chunks, rather than all at once. We can do about two weeks at a time. When we use the non-online version, we do one month at a time.
  6. Click "Search" to update the results. Scroll to the bottom, to make sure you have fewer than 500 lines.
  7. Click "Export", and save to download an excel version of your transactions.
  8. Find the file in Downloads, or wherever you download your internetty files to.

Processing the Vagaro Excel File for Transactions Pro Importer

  1.  Find the file "TransactionList.xls" in downloads, and move it to My Documents\Transactions Record Supplemental to QB (make this folder if you don’t have it)
  2. Rename the file by adding the date range of the transactions you are importing (something like TransactionList 5.1.13-5.14.13).
  3. Open the file in Excel (respond "yes" to the warning question).
  4. Save as a tab-separated text file.
  5. Navigate to http://davidlandy.net/VagaroToQBTransactions.html
  6. For the number, enter a large number based on the date, like 50120130001 (May 01, 2013, 0001).
  7. Upload your file
  8. Copy and paste the rows of the text file int a new .txt file. Include the header row, and all the data. We’ll use it when we import the transactions, below.

One thing to note about the importer: if a person uses cash and credit card, vagaro just uses both payment entries. However, each transaction in QB is constrained to use just one payment type.  The script uses the code "multiple methods" as the payment type in this situation, and relies on you to track these down before you record your credit card deposits. Caveat emptor!

Import the Transactions

  1. Open Quickbooks and open the company file. We use Internet Explorer and go to qbo.intuit.com.
  2. Open Transaction Pro Importer in the app center in Quickbooks.  The way I know to get to the app is to go to the app center, click on "Import Transactions", which will suggest you buy the app you already own. BUT the "my apps" button has now magically appeared, and you can click on it.
  3. Click on "Select a file", and chose the  excel file you just made by navigating to My Documents\Transactions Record Supplemental to QB, and picking it.
  4. Click on "Upload File"
  5. Select sheet 1.
  6. Select "Sales Receipt" as the Import Type.
  7. Align the columns as needed. If you save your map, you’ll only need to do this once.  Here’s the mapping we want:RefNumber — RefNumber
    Customer — Customer
    TxnDate — TxnDate
    PaymentMethod — PaymentMethod
    TaxAmt — TaxAmt
    TaxRate — TaxRate
    LineTaxable — LineTaxable
    LineItem — LineItem
    LineAmount — LineAmount
    BankAccount — BankAccount
  8. When your map works, save it.
  9. Be sure to check over the import down at the bottom of the page before importing. It is much easier to make changes now, than it is to make them later!!! Check the following  (The same ones you just checked before) columns:
    RefNumber
    Customer
    TxnDate
    BankAccount
    PaymentMethod
    TaxAmt
    LineTaxable
    LineItem
    LineAmount
  10. Select the rows to import. If something didn’t work, you have to do delete each transaction one at a time, so you might want to do a test import of two or three items to check them over.  If you do, just delete your test import (even if it worked), and import the whole batch.
  11. Select import, and wait for a long time.  You might want to go do something else.
  12. When your imports are finished, check them over.

 Whew! That’s it.  I hope you had fun!  Feel free to write with questions or comments. If you’re Vagaro, please feel free to make this easier on us by making an Excel option that

 

Obsolete Version: Processing the Vagaro Excel File for Transactions Pro Importer

  1.  Find the file "TransactionList.xls" in downloads, and move it to My Documents\Transactions Record Supplemental to QB (make this folder if you don’t have it)
  2. Rename the file by adding the date range of the transactions you are importing (something like TransactionList 5.1.13-5.14.13).
  3. Open the file in Excel (respond "yes" to the warning question).
    Note: For some reason, Vagaro produces very large excel files by default, so you may need to copy and paste the data into a new excel file (this sounds weird, but it totally works). Now is a good time to do that.Now we’re going to make a bunch of little, tiny changes to the workbook. I’m not providing any rationales, so they may seem totally mystifying.  Enjoy!!
  4. Delete the final line of the excel spreadsheet, which contains only summaries and totals
  5. Delete rows 2, 3, and 4 of the excel spreadsheet, which for some reason contains weirdly empty rows. I think this is again a problem in Vagaro’s export format.
  6. The tips need to be processed separately from the payments.  Select the entire table from row 2 on, and copy.  Paste it at the very final row. you should now have twice as many rows as you originally did (after the first row, called the "header" row).  Keep track of the row number between the two sets.  I usually color the first new row yellow.
  1. Select column C, and insert a new column by right-clicking.
    Label the row "PaymentMethod" in the first row (C1).
  2. In the second row (C2), enter =If(L2>0, if(N2>0, "Cash", if(O2>0, "Check", if(R2>0, if(U2="–", "Other Credit Card", if(U2="Other", "Debit Card", U2))))), "Cash").  This line guesses how the person paid, and made a single column for payments, just how Quickbooks likes it.
  3. Drag entry C2 down to the bottom of the table.
  4. Change the name (row 1) of column G (price) to "Rate".
  5. Insert a new column next to column H (to the Left).  Name this column LineAmount.
  6. Set the value of column H2 to =G2-J2.  Drag the value to the bottom of the table.  This new column calculates what a person actually paid for the service, minus any discounts
  7. Go to Row I.  Insert a row to the left and name it LineTaxable.
  8. Set row 2 of I2 equal to =if(and(D2<>"Tips", J2>0), "Y", "N"), and drag it down. If they paid a tax, it was taxable. We want to make sure not to tax tips!
  9. Go down to the row you kept track of earlier (the top repeated row).  Replace the amount in the Price column (this should be column H) with the amount in the tips column. To do this, you make the value "=O Something".  The something is whatever the row of your first repeated row is. If the first tips row is 292, you’d enter =O292. Drag this value down to the bottom of the table.
    17: In the top repeated row, change the item type (Column D) to "Tips".  Drag this value down to the bottom of the table.
    18: Go to column D, and insert a new column.  Name this column LineItem
  10. 19: In row D2, enter =Left(E2, 31).  Drag this value to the bottom of the table. Quickbooks 2012 requires line items to be less than 32 characters. I don’t know if newer versions of QB do this too, and haven’t checked.
    20: Change the following columns: Checkout Date (Column A) -> TxnDate,  Tax- (Column K) >TaxAmt. This matches the names that Transaction Pro Importer expects.
  11. Create a new column left of column J named BankAccount, and set it equal to whatever account you use to collect undeposited funds (you can just autodeposit these, but I doubt you want to). We use 230-Undeposited Funds.
  12. Create a new column left of column M, called TaxRate.  Set the value of this column to 0.05 (or whatever your state’s tax rate on taxable items is).  Excel may automatically make the numbers increase when you drag them. If it does, set two rows to 0.05, select both of them, and drag that down.
  13. Sort the table based on date.  In windows, click anywhere in column A, and select "sort A->Z".
  14. Select column A, and insert a new column.
  15. In the new cell A1, enter "RefNumber".
  16. In the cell below it (A2), enter a large number based on the date, like 50120130001 (May 01, 2013, 0001).
  17. In the cell below it (A3), enter =if(c3=c2, A2, A2+1), and drag to the bottom of the table.
  18. Finally, save the file using "save as", and save it to an excel workbook.

If you’re importing to the QB2012 version of Transaction importer, you have to do four things differently. First, the ‘refnumber’ cannot exceed 11 characters.  So make it slightly smaller than the above, like by dropping the "20″ (Y2k alert!). Second, include a column called ‘taxcode’, which is left of column M, and has the value =IF(L2="Y", "Tax", "Non").  Then make a TaxType column that is set to your tax type.  Ours is "Sales Tax–VDOT". Finally, when importing, don’t deposit to a bank account.

Of course, all of this could be made much easier with an awk or R script. I might write one some day, or Vagaro could fold all of these into a new "download for Import into Quickbooks" format we could just get from the website (are you listening, Vagaro?). But for now, this is the best way we’ve found. (UPDATE: I did write this script.  See above for details).