Tools – QuickBooks

General

AutoPal Software provides a Live integration with QuickBooks (QB). It connects directly through the QuickBooks WebConnector (QBWC) which is a product put out by Intuit. Basically, AutoPal has built an App for the QuickBooks WebConnector that will transmit data automatically as frequent as every 1 minute depending on the settings selected.

Limitations

The only limitations for this integration are those of Intuit’s QBWC. The QBWC does not connect with the Apple version of QB, or the Online version, or versions earlier than QuickBooks Pro 2003.

Set up process

General Setup Video:

Web Connector Setup:


Number1.png Download QBWC – Click the Download QuickBooks WebConnector link in Tools – QB Live – Setup inside of the AutoPal system

Number2.png Download AutoPal App – Click the Download File link in Tools – QB Live – Setup

Number3.png Open QuickBooks and the QuickBooks company file with which you want the QBWC to link

Number4.png Open the QBWC

Number1.png In the Windows search box type “web”. Click on “Exchange Data with Web Services” (This is the QBWC).

Number2.png On the QuickBooks WebConnector window click “Add an Application”

QuickBooks1.png

       Number3.png Navigate to the location to which you downloaded the AutoPal App for the QBWC it will be named my-quickbooks-wc-file.qwc. Open it.

QuickBooks2.png

       Number4.png On the Authorize New Web Service window click “OK”

QuickBooks3.png

       Number5.png After reviewing the Application Certificate choose to allow the application access to the QB company file either “Yes, whenever this QuickBooks company file is Open”, or “Yes, always” (this allows access even if QuickBooks is not running). Click “Continue” and then click “Done” on the Access Confirmation dialog box.

Number6.png Enter the password found at Tools – QB Live – Setup. Password in the QuickBooks WebConnector window.

QuickBooks4.png

       Number7.png Select both of the check boxes as seen above. You can change the frequency in which the Auto-Run processes to as frequent as every 1 minute. If you don’t have Auto-Run selected then you will need to click “Update Selected” each time you wish to run this connection manually.

Number8.png Your are connected!

Standard Errors

QBWC1039: There was a problem adding the application. Check QWCLog.txt for details. Unique OwnerID/FileID pair value required.

Solution – This means that you are already have the AutoPal QuickBooks Integrator linked with your QB file. You can change this by navigating in your QB file to Edit – Preferences – Integrated Applications – Company Preferences – if you have any version of “AutoPal QuickBooks Integrator” simply choose to remove those & then repeat step 3. from the setup process.

If this problem persists then you can open the downloaded QuickBooks App file & edit its OwnerID & FileID. Open the file in notepad. On lines 9 & 10 in the 1st set of numbers & letters change the 6th & 7th digit from Alpha, then numeric to something different with the same Alpha numeric order. Select A,B,C, or D. Repeat the same process for both line 9 & 10. Then save & retry the setup starting with step 3. (If you use this method then it will not validate the secure socket layer (SSL) certificate, but you will still be a secure connection).

Using QB Live Tools

Now that you have AutoPal Connected to the QuickBooks WebConnector & the QuickBooks WebConnector connected to your QuickBooks file you are ready to begin the process of transmitting data between the two. The connection is a limited two-way connection. Meaning that information added in AutoPal will be posted over to QuickBooks, & QuickBooks will return an ID to AutoPal so that AutoPal knows that information was successfully added to QuickBooks, however information added into QuickBooks will NOT be updated or added to AutoPal.

You can control the QuickBooks integration at a company level under Tools – QuickBooks Live – Setup. This area provides all the necessary links & tools for getting connected & using QuickBooks Live tools. The QB Password will remain the same as long as you are subscribed to the QuickBooks tools with AutoPal.

Queue Management Per Loan – this tab gives you the same management tools that you have on the QuickBooks tab inside of each loan account, but for all the loans in your company. You can search for the loan account that you want to control & then click the Edit button this will display the functions available for this loan.

Company QuickBooks Tools

  • Cut off Date – Here you can control a company cut off date. No transactions in AutoPal previous to this date will be sent to QuickBooks. This is most commonly used to close out accounting periods, etc.
  • QB Setup – this will send over chart of account items to your QB file. Please keep in mind AutoPal does not “map” to your existing chart of accounts, but rather inputs the necessary accounts. If you have an account with exactly the same name as the one AutoPal is trying to create, this will cause a conflict and the account will not link correctly. Please make sure your existing chart of accounts does not conflict with the one listed under the Chart of Accounts heading below. All of the standard configurations will automatically send over in the background once you click “QB setup” you do not have to do this manually.
  • QB Queue Clear – this will DELETE ALL PENDING TRANSACTIONS that have not yet been pulled into QuickBooks by the QBWC. Next time the AutoPal calculator runs on the accounts it will fill this queue back up with all unsent transactions.

Chart of Accounts

  • Bank: Type = Bank – a fake bank account that will be used as a clearing house. All funding checks will be issued from this account. When you actually print the check & send it to the source company you will need to do this from an actual bank account in QB & have the offsetting account for the transaction be this “bank” thus using it as a full clearing account. Doing it this way will allow you to easily track discounts, make sure you are tracking an accurate balance in your actual bank account, issue funding checks that may include multiple deals, and other clean GAAP processes.
  • Auto Notes Receivable: Type = Accounts Receivable – this will be the accounts receivable (A/R) account that offsets the issuing of the funding check. It will show as an A/R asset on your balance sheet. As principal is repaid by the borrower the Auto Notes Receivable balance will decrease based on your QB file configuration. Since payments made by borrowers can include interest and fees as well as principal, AutoPal automatically creates invoices for interest and fee income. QB automatically assigns all unpaid invoices to A/R, so that will not be included in the Auto Notes Receivable. Thus, the sum of the balances of the generic Accounts Receivable account and the Auto Notes Receivable account will be your total outstanding receivables balance. When payments are applied they often will be applied to a single A/R account so it is very common to have a negative A/R account and a positive Auto Notes Receivable account because your choice on application of payments is overpaying the A/R by the principal portion, which balances out nicely on your Balance Sheet.
  • Deferred Discount – type – Other Current Liability – if you are splitting discount & realizing it over time in the amortization schedule of the loan then this is the account that will hold the “unpaid” discount portion on the loans. As discount is realized over the term of the loan it will debit this account & move it to a discount income account. As new loans are funded & the funding check is shorted by the discount amount it will credit this Deferred Discount account by the discount amount.
  • Discount Income: Type = Income – if you do NOT split discount over the term of the loan then this account will be used when the funding check is written to fund a loan. The check will be shorted by the discount amount & that amount will be realized as income in the period that the loan is funded. If you do split your discount then as you realize the discount over the term of the loan it will move a chunk of each payment from the Deferred Discount account into the Discount Income account in the appropriate period. This will show as an Income on your P&L statements.
  • Fee Income: Type = Income – By default AutoPal will link each fee to a generic “Fee Income” account. If you wish to change this then manage the Items directly in QB to change which accounts they link to. This would allow you to see each type of fee you charge split into different sections inside of QB. You can also do this in an AutoPal report.
  • Interest Income: Type = Income – similar to the fee income AutoPal will link an Item on the invoice to an account from the chart of accounts. All interest invoices will link to this account. This is true regardless of interest application (between transactions, or between periods).

Items & Services

QuickBooks controls each item on an invoice linked to an account from the chart of accounts. By default all “fees” are linked to the “Fee Income” account. However as long as you keep the item name the same you can change what account it is linked to directly in QuickBooks. This will allow you to have a very customized P&L statement.

Please keep in mind that each item name MUST be unique. If the name already already exists among your Items, AutoPal will not know how to link with it. If you edit existing names, transactions between QuickBooks and AutoPal will no longer be linked, as they will get a connection error when sending to QB. The items created by AutoPal are:

  • Convenience Fee –
  • NSF Fee –
  • Late Fee –
  • Repossession Fee –
  • Other Fee –
  • Underwriting Fee –
  • Interest –

In addition to Items & Services you also have payment types that can come over such as (Cash, Check, Debit, eCheck, etc…)

General QuickBooks Background Information

AutoPal will send over all of the following: Funding Check – written to the source company, split between Auto Notes Receivable, Deferred Discount, or Discount Income. Payments Invoices (Interest & All Fee Types) General Journal Postings – discount moving from the Deferred Discount account into the Discount Income account. Vendors – these are the source companies to which you are writing checks. Customers – these are your borrowers. Reversals & Update of Transactions – if you reverse or in some way change a transaction in AutoPal if it affects other transactions such as the accrual of interest, discount split, reversal of a payment then AutoPal will update those other affected transactions UNLESS you have a cutoff date post an affected transaction, or if it is a payment if the payment is already deposited, then it will not remove or edit that payment. Configuration Recommendations – it is HIGHLY recommended that you run QB in Multi-User mode, that you have the QBWC running automatically every 1 minute & that you do NOT turn off your internet connection or computer each night.

QuickBooks Tab (Inside of each Loan Account)

The QuickBooks Tab shows the history of the loan account by transaction, similar to the “Transactions” tab. You can control QuickBooks settings for this loan and send over individual transactions in this tab. If the transaction is after the QuickBooks cutoff date (specified below for this loan and in Tools – QuickBooks Live for the company) then you will see links in the transaction line on the right. To send those transactions to QuickBooks click on the link (e.g. “Interest” or “Payment”). Click the “Del” link next to an item (except payments) to delete it from QuickBooks. To delete a payment from QuickBooks you can simply reverse the payment in the payments tab. If that payment has not been deposited within QuickBooks it will be deleted from QuickBooks.

When you click on this tab it runs AutoPals QuickBooks Calculator and will automatically send any transactions to QuickBooks that have not previously been sent & returned a transaction id.

In addition to having company level controls of your QB integration you also have the ability to manage each individual account & each transaction between AutoPal & QB. If the transaction already exists in QB then it will show a “del” next to the sending buttons. If the cutoff date is after these transaction dates then Only the “del” will show as you can’t send over transactions prior to that date. You will know that the transaction is in QB by having this “del” showing.

For many reasons that are out of AutoPal’s control it is possible that a transaction will not automatically send to QB. If this is the case then simply open that account & go to the QB tab, find that transaction & select the button you wish to send such as “Payment” or “Discount”, or “Interest”, etc.. You can also choose to delete if desired. Please keep in mind as long as the account has the “Sync with QB” checked then these linking will occur automatically.

  • Reset Loan – This deletes the link between this account and the corresponding account in QuickBooks. If you want to resend the account to QuickBooks, you will need to edit the account name in QuickBooks so that when you send this account again, QuickBooks doesn’t see it as a duplicate account and disregard it. When the account is resent to QuickBooks, all transactions after the QuickBooks Cutoff Date will also be sent.
  • Account Setup – This will send the customer information and create the account in QuickBooks. It will also send the funding check information and create an invoice for the underwriting fee. This is not restricted by cutoff dates. You should click this command as part of setting this loan up to send to QuickBooks.
  • Send Customer – This will send only the customer information and create the customer account in QuickBooks. If you choose to send another transaction, if the customer information has not been sent to QuickBooks, the customer information will be sent before the transaction is sent. You should also click this command as part of setting this account up for QuickBooks.
  • All Transactions – All transactions will send all transactions if they have not been successfully sent to QuickBooks previously. This includes payments which are usually sent only when the payment is logged.

Troubleshooting

In general nearly all connection issues occur when at least one of the elements of the transaction that is trying to go into QB is missing. That could be a chart of accounts name, customer name, payment method name, etc… Try to isolate what is having the problem & work backwards to see what element is missing that would cause the problem that you are having.

If you are having problems with a particular account coming over to QB and the problem is isolated to a single account then reset the loan. Change the name of that customer if QB to anything else then send over the loan again to QB then merge the two.

If you are having problems with a certain type of transaction coming over to QB then check the naming on all items that would be in that transaction & see if something changed. Such as someone change the name of a chart of account item, etc.

If you are having problems with a certain payment type (cash, check, etc) then check to see if you have that value configured on your QB as a valid payment method.

One common Error is if you have installed the QBWC & AutoPal’s Application prior that you will receive an error of “Unique OwnerID/FileID Required” — if you get this error then try these steps.

   Open Notepad
   File > Open
   Change the 'Files of Type' drop-down to 'All Files'
   Choose your .QWC file
   Change a single character in the OwnerID string, and a single character in the FileID string. 
   You can change any of the 0-9, A-F characters to any other character in the range 0-9, A-F. 
   It does not matter which character you change. If the rest of the characters are lowercase, 
   make it lowercase. If the rest of the characters are UPPERCASE, make your new character UPPERCASE.
   File > Save
   Re-load the modified file into the Web Connector

Customer Account Not sending to QB — the most common error for this is that the customer already exists in QB. However if that is not true then it may be that the name of the customer is too long. 3070 – STRING_TOO_LONG -The data in a string field in the requests exceeds the maximum characters allowed for that field.

Debugging Resources

Sending Vendors

One common error or mistake that users make when sending vendors from AutoPal is that they have already setup that same vendor in QB. AutoPal will try to send over the vendor but will receive a response from QB that states that it can not create this Vendor because it already exists. To eliminate this problem simply alter the name of the Vendor in QB or in AutoPal, (add a . at the end of the name) then choose to send to QB. You can always choose to merge those two Vendors once in QB (make sure you keep the name that is in AutoPal so it will know the account to reference).

Related Articles

Leave A Comment?