{"id":2170,"date":"2019-10-18T12:11:37","date_gmt":"2019-10-18T17:11:37","guid":{"rendered":"https:\/\/flexiblefinancials.com\/?p=2170"},"modified":"2021-10-15T17:21:34","modified_gmt":"2021-10-15T22:21:34","slug":"options","status":"publish","type":"post","link":"https:\/\/flexiblefinancials.com\/index.php\/2019\/10\/18\/options\/","title":{"rendered":"Loading Transaction History into QuickBooks"},"content":{"rendered":"\n<p>You\u2019ve decided to download transaction history from your bank and credit card companies.&nbsp; How are you going to get that data into QuickBooks?&nbsp; And how will you ensure each transaction ends up posted to the right account?<\/p>\n\n\n\n<p>In this post, I want to offer several suggestions to help you decide how to load your data.&nbsp; There are plenty of options, and some will work better in your situation than others.<\/p>\n\n\n\n<p>Start with an example.&nbsp; Say your bank gives you the option of downloading checking account transactions, and you\u2019d like to post the last 18 months.&nbsp; The download comes in QBO format for the past 90 days, or CSV format for the past 18 months.&nbsp; You can include the date, amount, and a description field with a mixture of data in it that varies with the type of transaction.<\/p>\n\n\n\n<p>First, note that options differ for importing history into Desktop and Online versions of QuickBooks.&nbsp; We\u2019ll consider these cases separately.<\/p>\n\n\n\n<p><strong><u>QuickBooks Desktop<\/u><\/strong><\/p>\n\n\n\n<p>1)&nbsp; <strong>Accountant\u2019s Edition<\/strong>.&nbsp; If you have access to QuickBooks Premiere Accountant Edition, you can download bank data into Excel and import it directly into QuickBooks.&nbsp; <strong>This is a great option and I highly recommend it as your first choice.<\/strong><\/p>\n\n\n\n<p>What you can import:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Types of transactions: Checks, Deposits, Credit Card Charges, invoices, and Bills<\/li><li>Fields: You can upload most of the fields you\u2019d ever need to populate.&nbsp; For example, with checks this includes account, class, payee, memo, customer:job, billable status, and check number.<\/li><\/ul>\n\n\n\n<p>There are certain restrictions:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Editions: Let\u2019s say someone with QuickBooks Pro asks you to help load history using your Accountant\u2019s Edition. Both of your QuickBooks Editions must be in the same version (year).<\/li><\/ul>\n\n\n\n<p><em>For example, you can\u2019t begin with data in 2018 QuickBooks Pro, process the history using 2019 Accountant\u2019s Edition, and send the file back to 2018 QuickBooks Pro.&nbsp; You\u2019d need to have both Editions in either 2018 or 2019 versions.<\/em><\/p>\n\n\n\n<p>How to learn more:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>You can learn more about using this feature here.<\/li><\/ul>\n\n\n\n<p>2)&nbsp; <strong>Third Party Utilities<\/strong>.&nbsp; If you can\u2019t use the Accountant Edition approach, another option is to buy third party software that facilitates batch data entry.<\/p>\n\n\n\n<p>Vendors include:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/cloudbusinesshq.com\/\" target=\"_blank\" rel=\"noopener noreferrer\"><span style=\"color: #0000ff;\">CloudBusiness<\/span><\/a><\/li><li><span style=\"color: #0000ff;\"><a style=\"color: #0000ff;\" href=\"https:\/\/www.transactionpro.com\/\" target=\"_blank\" rel=\"noopener noreferrer\">Transaction Pro<\/a><\/span><\/li><li><span style=\"color: #0000ff;\"><a style=\"color: #0000ff;\" href=\"https:\/\/www.zed-systems.com\/\" target=\"_blank\" rel=\"noopener noreferrer\">zedsystems<\/a><\/span><\/li><\/ul>\n\n\n\n<p>3)&nbsp; <strong>iif Files<\/strong>.&nbsp; A third option is to convert your data into iif transaction format and import it into QuickBooks.<\/p>\n\n\n\n<p>What you can import:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Types of transactions: Most transactions you\u2019d ever wish to import.<\/li><li>Fields: Most of the fields you\u2019d ever wish to import for any given transaction.<\/li><\/ul>\n\n\n\n<p>There are certain restrictions:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The use of iif files requires a certain degree of experience and patience. You\u2019d be wise to read up on how to do it, and to ensure you\u2019ve properly backed up your data file before proceeding.<\/li><\/ul>\n\n\n\n<p>How to learn more:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Explore Intuit&#8217;s iif utility toolkit <span style=\"color: #0000ff;\"><a style=\"color: #0000ff;\" href=\"https:\/\/quickbooks.intuit.com\/learn-support\/en-us\/manage-lists\/iif-overview-import-kit-sample-files-and-headers\/00\/201577\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>.<\/span><\/li><li>Intuit changed their tools for importing iif files starting in 2019. Access information from Intuit <span style=\"color: #0000ff;\"><a style=\"color: #0000ff;\" href=\"https:\/\/quickbooks.intuit.com\/learn-support\/en-us\/data-utilities\/improved-iif-import-in-quickbooks-2019\/00\/203267\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>.<\/span><\/li><li>Reviews of the 2019 changes have been mixed.&nbsp; For one review click <span style=\"color: #0000ff;\"><a style=\"color: #0000ff;\" href=\"https:\/\/support.hubstaff.com\/import-iif-files-quickbooks-2019-qb-2019-enterprise-19-0-2\/\" target=\"_blank\" rel=\"noopener noreferrer\">here.<\/a><\/span><\/li><\/ul>\n\n\n\n<p>I have a free utility program that converts Excel data into a general ledger formatted iif transaction. &nbsp;<span style=\"color: #0000ff;\"><a style=\"color: #0000ff;\" href=\"https:\/\/flexiblefinancials.com\/index.php\/contact\/\">Contact me<\/a> <\/span>for more information.&nbsp; Instructions appear on the first tab of the Excel workbook.&nbsp; Plan to use this tool at your own risk as Flexible Financials will not be responsible for its use outside of a Flexible Financials consulting engagement.<\/p>\n\n\n\n<p><strong><u>QuickBooks Online<\/u><\/strong><\/p>\n\n\n\n<p>QBO will accept transactions from:<\/p>\n\n\n\n<p>1)&nbsp; A direct connection between QBO and your bank<\/p>\n\n\n\n<p>2)&nbsp; Files in QBO format downloaded from your bank and uploaded into QBO.<\/p>\n\n\n\n<p>3) &nbsp;Files in CSV format downloaded from your bank and uploaded into QBO.<\/p>\n\n\n\n<p>Note that QBO does not accept iif files.&nbsp; Use of iif files is limited to QuickBooks Desktop.<\/p>\n\n\n\n<p>Whatever your data source, all the transactions you upload to QBO flow through the same bank import module you\u2019d use to process your daily bank downloads.&nbsp; This means that QBO will only accepts the following fields:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Date<\/li><li>Description*, and<\/li><li>Amount<\/li><\/ul>\n\n\n\n<p>* QBO normally copies bank detail information into the memo fields of downloaded transactions, and this can vary somewhat from the information on your bank statement. &nbsp;For simplicity, I\u2019m using the term Description to include whatever information of this type appears in your feed.<\/p>\n\n\n\n<p>Why not accounts or classes?&nbsp; The bank import module assumes you\u2019ll assign accounts and classes in one of three ways:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>By matching each transaction to one you\u2019ve already entered (which, of course, doesn\u2019t work if you\u2019re importing a history file as your original data feed)<\/li><li>By classifying transactions one at a time in the banking module, or<\/li><li>By processing transactions using QBO rules.<\/li><\/ul>\n\n\n\n<p>QBO\u2019s rules are extremely flexible, and it will be your first choice for assigning accounts and classes to historical data.&nbsp; There are plenty of great resources online showing you how to set up rules, so I won\u2019t cover this information again in this post.<\/p>\n\n\n\n<p>What I will address, however, is the situation where you want to assign accounts or classes in Excel before uploading your data.&nbsp; The question then arises, how can you get QBO to accept these fields? There are two choices:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Employ a workaround solution (described below), or<\/li><li>Buy a third-party utility that bypasses the bank import module.<\/li><\/ul>\n\n\n\n<p>Let\u2019s look at these two options.<\/p>\n\n\n\n<p><strong><u>The Workaround<\/u><\/strong><\/p>\n\n\n\n<p>Let\u2019s say your data downloads from the bank in CSV format. &nbsp;When you open it in Excel, it looks like this.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>Col A<\/td><td>Col B<\/td><td>Col C<\/td><td>Col D<\/td><td>Col E<\/td><\/tr><tr><td>Date<\/td><td>Description<\/td><td>Amount<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td>6\/15\/2019<\/td><td>IMX Corp 593-88542<\/td><td>18.23<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td>6\/16\/2019<\/td><td>CHEVRON 597 562-888-1212<\/td><td>56.42<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td>6\/17\/2018<\/td><td>CHEVRON 597 562-888-1212<\/td><td>10.49<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>I\u2019ve had clients sort this info by payee, then copy accounts into Col D as shown below.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>Col A<\/td><td>Col B<\/td><td>Col C<\/td><td>Col D<\/td><\/tr><tr><td>Date<\/td><td>Description<\/td><td>Amount<\/td><td>Account<\/td><\/tr><tr><td>6\/16\/2019<\/td><td>CHEVRON 597 562-888-1212<\/td><td>56.42<\/td><td>6240 Vehicle Gas<\/td><\/tr><tr><td>6\/17\/2018<\/td><td>CHEVRON 597 562-888-1212<\/td><td>10.49<\/td><td>7240 Travel Meals<\/td><\/tr><tr><td>6\/15\/2019<\/td><td>IMX Corp 593-88542<\/td><td>18.23<\/td><td>7115 Office Supplies<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>From here, you can add a column F to hold a modified description incorporating the account information, as follows:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>&nbsp;<\/td><td>Col A<\/td><td>Col B<\/td><td>Col C<\/td><td>Col D<\/td><td>Col E<\/td><\/tr><tr><td>Row 1<\/td><td>Date<\/td><td>Description<\/td><td>Amount<\/td><td>Account<\/td><td>New Description<\/td><\/tr><tr><td>Row 2<\/td><td>6\/16\/2019<\/td><td>CHEVRON 597 562-888-1212<\/td><td>56.42<\/td><td>6240 Vehicle Gas<\/td><td>Acct 6240 CHEVRON 597 562-888-1212<\/td><\/tr><tr><td>Row 3<\/td><td>6\/17\/2018<\/td><td>CHEVRON 597 562-888-1212<\/td><td>10.49<\/td><td>7240 Travel Meals<\/td><td>Acct 7240 CHEVRON 597 562-888-1212<\/td><\/tr><tr><td>Row 4<\/td><td>6\/15\/2019<\/td><td>IMX Corp 593-88542<\/td><td>18.23<\/td><td>7115 Office Supplies<\/td><td>Acct 7115 DEPT 02 CHEVRON 597 562-888-1212<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>You populate column E using Excel\u2019s CONCATENATE command.&nbsp; The formula for cell E2 is:<\/p>\n\n\n\n<p>E2 = CONCATENATE(\u201cACCT\u201c,\u201d \u201c,LEFT(D2,4),\u201d \u201c,B2).<\/p>\n\n\n\n<p>When it comes time to upload the description, you\u2019d use column E for the description instead of column B.&nbsp; Then you\u2019d write a QBO rule for that account.&nbsp; For example, you would create a rule looking for the text \u201cACCT 6240\u201d and classify those expenses to account 6240.&nbsp; You\u2019ll need one rule for each account.<\/p>\n\n\n\n<p>Why would you do this in Excel instead of simply making the account assignment within QBO in the first place? There simply are times when Excel handles this better:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>One case is where you have a lot of transactions to classify and automatically assigning accounts requires several logical decisions. For example, you might have a situation where you need to consider three factors in sequence.&nbsp; You could add four columns to your download:&nbsp; Columns F, G, H, and I.&nbsp; Col F is a Y\/N flag set based on the card used.&nbsp; Col G is a Y\/N flag based on the state where a purchase occurred.&nbsp; Col H is a yet another flag based on the date, since the company reorganized halfway through the year and department assignments changed.&nbsp; Finally, Col I is an IF\/THEN\/ELSE formula based on Cols F-H.&nbsp; By bringing this answer back into Col D, you\u2019d be able to mass upload accounts in a way that would be more difficult to code using QBO rules.<\/li><li>Another case is where you\u2019re dividing the work of assigning codes among several people and it\u2019s just faster to copy\/paste the answers into a single file that you then upload.<\/li><\/ul>\n\n\n\n<p><strong><u>Third Party Utilities<\/u><\/strong><\/p>\n\n\n\n<p>There are several companies offering utilities to send transactions from bank downloads or Excel into QBO.&nbsp; <span style=\"color: #0000ff;\"><a style=\"color: #0000ff;\" href=\"https:\/\/www.propersoft.net\/\" target=\"_blank\" rel=\"noopener noreferrer\">ProperSoft<\/a> <\/span>is one company,&nbsp;<a href=\"http:\/\/zacharysystems.com\/\"><span style=\"color: #0000ff;\">Zachary Systems<\/span><\/a> is another.&nbsp; I have not used these tools so I can\u2019t vouch for their effectiveness.&nbsp; You can search online for reviews and pricing.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You\u2019ve decided to download transaction history from your bank and credit card companies.&nbsp; How are you going to get that data into QuickBooks?&nbsp; And how will you ensure each transaction ends up posted to the right account? In this post, I want to offer several suggestions to help you decide how to load your data.&nbsp; &#8230; <a title=\"Loading Transaction History into QuickBooks\" class=\"read-more\" href=\"https:\/\/flexiblefinancials.com\/index.php\/2019\/10\/18\/options\/\" aria-label=\"Read more about Loading Transaction History into QuickBooks\">Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2170","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/flexiblefinancials.com\/index.php\/wp-json\/wp\/v2\/posts\/2170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/flexiblefinancials.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/flexiblefinancials.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/flexiblefinancials.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/flexiblefinancials.com\/index.php\/wp-json\/wp\/v2\/comments?post=2170"}],"version-history":[{"count":7,"href":"https:\/\/flexiblefinancials.com\/index.php\/wp-json\/wp\/v2\/posts\/2170\/revisions"}],"predecessor-version":[{"id":4992,"href":"https:\/\/flexiblefinancials.com\/index.php\/wp-json\/wp\/v2\/posts\/2170\/revisions\/4992"}],"wp:attachment":[{"href":"https:\/\/flexiblefinancials.com\/index.php\/wp-json\/wp\/v2\/media?parent=2170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/flexiblefinancials.com\/index.php\/wp-json\/wp\/v2\/categories?post=2170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/flexiblefinancials.com\/index.php\/wp-json\/wp\/v2\/tags?post=2170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}