Tag: QuickBooks

Using VBA to create import files for QuickBooks

A couple of years ago I created some spreadsheets to help me migrate data into QuickBooks. I set up a worksheet that followed the IIF format and then saved it as a tab delimited file. The biggest problem was that one row of data required three rows in the IIF format. If you try and use copy and paste method to adding new rows, this can become quite a pain. The addressing is all screwed up. I figured out how to do it with some Excel functions(e.g. rows and offset) but the final result was still troublesome.

I finally bit the bullet and decided to use VBA to dynamically create a new worksheet in IIF format. I select the rows I am interested in and then I let the macro iterate through the selection creating IIF transactions in the worksheet. After I did a lot of searching for VBA examples I had a macro that worked remarkably well. It is a very simple program but it took me a while to find the right commands. Although the macro was a success, I still had problems with the IIF invoice transaction was not doing what I expected it to do when QuickBooks imported it. It was “in” but I had to fix a lot of transactions. This was hardly the time saver I hoped for.

My next step in the evolutionary process was to switch from IIF to an AutoIT script. I chose to modify the VBA macro to write out the data as an AutoIT array. This allowed me to use an include statement in my main AutoIT script to include the file containing the AutoIT code for the array. Since I run the script interactively, this was an easy way to get the data into the program. The good news is that after fine tuning the script I was able to achieve a pretty high success rate with the invoices. Part of the problem was with the data but a more challenging problem was the timing issues. Interactive scripting is still closer to an art form than a science. The script is not perfect but it is an improvement. Another advantage of using AutoIT is that I was able to further extend the scripting process to  let it partially fill out the QuickBooks Receive Payments screen. The script would wait for me to manually match invoices before continuing.  QuickBooks does not have an IIF transaction for receive payments.Since this traditionally is a manual process in QuickBooks, this is a big time saver.

Today I wrote another macro using my previous work on the macros as my example. Within a fairly short period of time I was able to create a new VBA macro to write out a worksheet of bills in the IIF format. I could of chosen to write an AutoIT script but I remembering having good success with importing IIF bills. Once again this will save me a lot of manual data entry and will undoubtedly make the bills more accurate since it is important that the memo fields get filled out with the proper billing information.

Importing invoices into QuickBooks using AutoIt Script

For several years I have successfully used the QuickBooks IIF format to import bills, checks, and employee hours. I have been less successful with invoices and I was unable to import payments. Several times I considered writing a C# or VB program to import the data but I backed off because it looked like it would take too much time. I looked at AutoIt but felt it would have a steep learning curve, too. Yesterday I decided to give AutoIt a try and after a couple of hours I was able import both invoices and payments.

Since I am the treasurer for a Habitat for Humanity affiliate I have a strong need to make sure our mortgage tracking system is kept in sync with QuickBooks accounts. I use an Excel spreadsheet to reconcile mortgage payments we record in the specialized system with the actual bank deposits. This is where I find the errors since we use one person to record the payments and another person to record the deposits. For each mortgage payment I must allocate it to principal, escrow, and late fee. If everything works fine, then both systems have the correct values for each individual home owner’s mortgage and escrow accounts. I set up an invoice for the principal owed when the home owner closes on the house. Every month I enter invoices for the escrow and late fees due. Habitat does not charge interest. On an average month I need to enter 30 to 40 invoices and 30 to 40 payments. As you can see, I have a vested interest in seeing that this project be quick and accurate.

The present script is a work in progress. I feed the data into the script via a VBA macro I created in my spreadsheet. The macro writes out the payment values as AutoIt code onto a new worksheet. Then I paste this code into my AutoIt script, save the script, and run it. Crude but effective. The code for the invoice screen was pretty straightforward and I am letting it run automatically. The code for the payment requires human interaction since I must allocate the payment to the different invoices. It pre-fills the payment screen and then asks me to allocate the payment and save the payment before proceeding. I click on a popup to process the next payment.

Before next month I will have the macro writing out the code to a text file that I can “include” in the script and include a little bit more error checking.

To budget or to not budget, that is the question?

I just got through making a comprehensive budget/cash forecast for our local Habitat affiliate. I made a budget for affiliate two years ago but I was dissatisfied with the results. I plugged the historical values from our income statement into a spreadsheet and after a few calculations I plugged the resulting values back into QuickBooks. QuickBooks integrates the budget into a couple of reports but the focus is on the income sheet. I published the standard QuickBooks Budget report for a year but we spent few words discussing it except for the fact that we were not raising enough funds to support our proposed construction plan. The budget accomplished little besides creating work for me because it focused on income and expense acoounts and the key Habitat board decisions concern asset accounts. Since then I have thought a lot about budgeting. What I consider to be key indicators for the affiliate led me to a refined conclusion. We need a budget to support board decisions on whether we should build houses, purchase land, and expand our building capacity by hiring personnel. Our capital expenditure plans are directly related to our ability to raise funds and our cash flow.

For a Habitat affiliate the most important cash flows, mortgages receivable, home owner principal payments and construction costs, are asset accounts and do not appear on the income statement. As a result a “useful” budget for a Habitat affiliate must establish goals not only for the income and expense accounts but for the mortgage receivable, construction in process, and land inventory accounts. The first time I made a budget I “winged” it in this area because the construction plan was not available. I knew this was important information but you cannot budget items you do not have a plan for. Since then I have convinced most of the board on the importance of this part of the planning process. Our cash situation is much tighter than two years ago so there considerably less wiggle room. The board placed a much higher priority on planning and executing the plan when I told them we did not have the money.

The budget is almost complete for this year. I modeled the format of the budget on the existing QuickBooks Income and Cash Flow statements. I did re-arrange the Cash Flow statement to highlight the essentials, fund raising/operating costs, home owner principal payments, and construction expenditures. I am going to use spreadsheets to create future financial statements for future time periods that look a lot like the existing statements. Although there are existing budget reports in QuickBooks, they do not model the cash flow adequately. Although I loathe to use reports outside of QuickBooks because it will take additional time on my part, the budget reports in QuickBooks do not do the job for us because of our cash flow forecasting needs.

I used this tutorial, Tutorial: Modeling and What if? analysis with pivot tables – 20 Jul 2006 , on pivot tables to provide me with an easier to use mechanism to summarize data. Basically I have a row with monthly values for all of the pertinent accounts I need to reconstruct the income and cash flow statements. I started off by modeling the first nine months of this fiscal year and making sure it matched existing accounting reports. Then I added three more rows for the last quarter and estimated the values. We should have a good cash forecast for the fiscal year end. My final trick is to model a minimal balance statement.

Putting together a construction plan for the next fiscal year is going to be difficult since the construction/fund raising plan is still in a state of flux. We have land we do not want to build on and land we cannot build on without significant capital improvements. This probably means we need to buy additional lots. The best I can do is guess on the plan. This should be beneficial to the board since they tend to focus best when I give them construction start and finish dates. By tying together the fund raising plan to our construction plan via the cash flow statement, the board should keep focused on the key issues.

Problems importing IIF transactions into QB2004

A while back I wrote a post called, “Update to Excel Macro to import IIF transactions“. In it I talked about a macro I was working on to import IIF transactions into QuickBooks. I had intended to share the macro once I got finished testing it. Well, I have finished testing it. There is good news and bad news. The good news is that the macro works as intended. The bad news is that although the transactions will import into QuickBooks, QB2004 does something funny with the transactions and I have not figured why. As an example if I import a late fee invoice, the late fee does not show up on the income statement even though I can see and edit the invoice. If I delete the line on the invoice and re-enter the line, the income will appear on the income statement. Obviously this is not the answer I was looking for.

Since then I have downloaded the SDK and am looking at modifying one of their sample programs to be a import program. With my workload this problem has not been high on my priority list. For whatever its worth, here is the macro as a text file.

Create IIF Rows from selection

Getting QuickBooks to run as non admin on a stand alone workstation setup

With a shout out to Mike in the blog comments… he said “I was troubleshooting an installation of QB07 on a 2003 Terminal Server. I had all the security modifications that I have used for the previous versions and it still wouldn’t work. What I discovered is that normal users don’t have but need rights to: Documents and Settings\All Users\Application Data\Common Files\Intuit\QuickBooks\qbregistration.dat I added rights to that file and the program ran properly. Bad news about having to be logged in at the server for the Database Manager. When I called support before installing on our server they never mentioned that to me. I was initially happy they had come up with a solution that didn’t require running full QuickBooks on my server. For the 2006 version I had installed QB running the Database piece on a different computer with a drive mapped to the server and it work fine for us. I may have to uninstall the 07 version and try that trick again.”

I originally could not get QuickBooks 2007 to run without admin rights on a stand alone machine… one I gave “Users” Full control to Documents and Settings\All Users\Application Data\Common Files\Intuit\QuickBooks\qbregistration.dat just like Mike said… it worked like a champ.

The official way to do it from the Intuit folks is to flip it so that the QB runs as a “service”

http://www.threatcode.com/quickbooks_2007.htm

Look at the screen shots here and do that extra step of changing the permissions of the “dat” file.

Share this post: email it! | bookmark it! | digg it! | live it!

Link to Getting Quickbooks to run as non admin on a stand alone workstation setup

–> I blogged this to keep track of the best way to install QB07 and run it as a normal user until Intuit updates their documentation.