Import bank transactions to QuickBooks desktop

Intuit disabled QuickBooks' function of retrieving bank transactions in May 2023 in order to push people to subscribe to their expensive online edition ($60/month).

QuickBooks can import qbo files. If a bank allows only ofx files, one usually just need to modify these files slightly to make them importable to QuickBooks Desktop - converting it to a qbo file. Unfortunately, Intuit is nice enough to disable qbo import. The only option is converting a qbo or an ofx file to an IIF file. One can pay over $100/year to use a utility program to convert these files. 

A nice fellow started to share his great tool QuickBooks QBO / CSV to IIF converter in early 2024. It saves time and prevents typos. It has many functions. Its basic functions should suffice for banking accounts with no more than a few dozens of transactions per month.

After downloading and starting the app, do the following monthly.

  1. Export the transactions of the past month to a qbo file. 
  2. Start the app and enter the default account (e.g., the most frequently used account such as "Sales - Software").
  3. Click but "Load QBO/QFX" to load the qbo file.
  4. Uncheck the transactions already in QB (e.g., tax payments generated from QB, overlap with the previous monthly import).
  5. Click the account column for each transaction which account is not the default account, and enter the account name (e.g., Computer and Internet Expenses, Payroll Expenses).
  6. Click button "Convert to IIF" to create the IIF file.
  7. In QB: File > Utilities > Import... to import the newly created IIF file.

The entire process should take no more than 10 minutes. 

 

 

 

IIF files are ASCII text, TSV (Tab-Separated Value) files. It is one of the worst document formats. It should be avoided whenever possible.

OFX file:

<OFX>            <!-- Begin request data -->
    <SIGNONMSGSRSV1>    <!--Signon Message Set -->
        <SONRS>          <!--The signon record identifies and authenticates a user to an FI -->
            <STATUS>      <!--Status -->
            <CODE>0      <!--0 = no error, 2000 = general error, 2022 = Invalid TAN (ERROR) -->
            <SEVERITY>INFO
            </STATUS>
            <DTSERVER>20230901113405.000[0:GMT]  <!-- Date and time of the server response, datetime-->
            <LANGUAGE>ENG                  <!-- Language used for text -->
            <DTPROFUP>20050531040000.000[0:GMT]  <!-- Date and time of last update to profile information for any service supported by this FI 
            <FI>            Financial-Institution-identification aggregate
                <ORG>CommerceBank    <!-- Name of ID owner -->
                <FID>1002                    <!-- Actual ID -->
            </FI>
            </SONRS>
        </SIGNONMSGSRSV1>
    <BANKMSGSRSV1>   
        <STMTTRNRS>          <!-- Begin response -->
            <TRNUID>0          <!-- Client ID sent in request -->
            <STATUS>
                <CODE>0
                <SEVERITY>INFO
            </STATUS>
            <STMTRS>              <!-- Begin statement response -->
            <CURDEF>USD
        <BANKACCTFROM>      <!-- Identify the account -->
                <BANKID>211344445              <!-- Routing transit or other FI ID -->
                <ACCTID>8886746666             <!-- Account number -->
                <ACCTTYPE>CHECKING            <!-- Account type -->
        </BANKACCTFROM>

        <BANKTRANLIST>                          <!-- Begin list of statement trans. -->
            <DTSTART>20230711040000.000  <!-- Start date: Jul. 11, 2023-->
            <DTEND>20230831040000.000      <!-- End date: Aug. 31, 203 -->
            <STMTTRN>                                  <!-- First statement transaction --> 
                <TRNTYPE>DEBIT                      Transaction Type
                <DTPOSTED>20230831040000.000    <!-- Posted on Aug. 31, 2023 -->
                <TRNAMT>-25.00                        <!-- -$25.00 -->
                <FITID>20230831000000025001  <!-- Unique ID -->
                <NAME>MAINTENANCE FEE           Name
                <MEMO>MAINTENANCE FEE          Memo
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>CREDIT
                <DTPOSTED>20230831040000.000
                <TRNAMT>3.53
                <FITID>20230831000000003532
                <NAME>AMAZON.COM.CA, I PAYMENT
                <MEMO>AMAZON.COM.CA, I PAYMENT
            </STMTTRN>
 
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230830040000.000
                <TRNAMT>-157.00
                <FITID>20230830000000157001
                <NAME>COMM OF MASS EFT MA DOR PAY
                <MEMO>COMM OF MASS EFT MA DOR PAY
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>CREDIT
                <DTPOSTED>20230830040000.000
                <TRNAMT>350.00
                <FITID>20230830000000350002
                <NAME>SBB MDEPOSIT
                <MEMO>SBB MDEPOSIT
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230829040000.000
                <TRNAMT>-783.44
                <FITID>20230829000000783441
                <NAME>IRS              USATAXPYMT
                <MEMO>IRS              USATAXPYMT
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230829040000.000
                <TRNAMT>-1359.83
                <FITID>20230829000001359832
                <NAME>Online Xfer Transfer to CK x6341
                <MEMO>Online Xfer Transfer to CK x6341
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230828040000.000
                <TRNAMT>-255.54
                <FITID>20230828000000255541
                <NAME>CITI AUTOPAY     PAYMENT
                <MEMO>CITI AUTOPAY     PAYMENT
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230828040000.000
                <TRNAMT>-1359.83
                <FITID>20230828000001359832
                <NAME>Online Xfer Transfer to CK x6341
                <MEMO>Online Xfer Transfer to CK x6341
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230816040000.000
                <TRNAMT>-537.11
                <FITID>20230816000000537111
                <NAME>HSA              HEALTH INS
                <MEMO>HSA              HEALTH INS
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>CREDIT
                <DTPOSTED>20230816040000.000
                <TRNAMT>448.29
                <FITID>20230816000000448292
                <NAME>MICROSOFT        0003846425
                <MEMO>MICROSOFT        0003846425
            </STMTTRN>
 
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230726040000.000
                <TRNAMT>-135.66
                <FITID>20230726000000135661
                <NAME>CITI AUTOPAY     PAYMENT
                <MEMO>CITI AUTOPAY     PAYMENT
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230726040000.000
                <TRNAMT>-783.44
                <FITID>20230726000000783442
                <NAME>IRS              USATAXPYMT
                <MEMO>IRS              USATAXPYMT
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230726040000.000
                <TRNAMT>-1359.83
                <FITID>20230726000001359833
                <NAME>CHECK # 298
                <MEMO>CHECK # 298
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230726040000.000
                <TRNAMT>-1359.83
                <FITID>20230726000001359834
                <NAME>CHECK # 297
                <MEMO>CHECK # 297
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230726040000.000
                <TRNAMT>-1359.83
                <FITID>20230726000001359835
                <NAME>CHECK # 296
                <MEMO>CHECK # 296
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230726040000.000
                <TRNAMT>-1359.83
                <FITID>20230726000001359836
                <NAME>CHECK # 295
                <MEMO>CHECK # 295
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>CREDIT
                <DTPOSTED>20230726040000.000
                <TRNAMT>2750.00
                <FITID>20230726000002750007
                <NAME>SBB MDEPOSIT
                <MEMO>SBB MDEPOSIT
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230718040000.000
                <TRNAMT>-537.11
                <FITID>20230718000000537111
                <NAME>HSA              HEALTH INS
                <MEMO>HSA              HEALTH INS
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>CREDIT
                <DTPOSTED>20230717040000.000
                <TRNAMT>449.26
                <FITID>20230717000000449261
                <NAME>MICROSOFT        0003785124
                <MEMO>MICROSOFT        0003785124
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>CREDIT
                <DTPOSTED>20230717040000.000
                <TRNAMT>617.79
                <FITID>20230717000000617792
                <NAME>GOOGLE           MULTIPLE_S
                <MEMO>GOOGLE           MULTIPLE_S
            </STMTTRN>
            <STMTTRN>
                <TRNTYPE>DEBIT
                <DTPOSTED>20230711040000.000
                <TRNAMT>-44.99
                <FITID>20230711000000044991
                <NAME>VERIZON          PAYMENTREC
                <MEMO>VERIZON          PAYMENTREC
            </STMTTRN>
        </BANKTRANLIST>

        <LEDGERBAL>                <!-- Ledger balance aggregate -->
            <BALAMT>66666.6      <!-- Bal amount: $66666.6  -->
            <DTASOF>20230901063405.495[-4:EDT]   <!-- Bal date: 09/01/2023, 06:34 am -->
        </LEDGERBAL>
        <AVAILBAL>                                <!-- Available balance aggregate -->
            <BALAMT>66666.6                  <!-- Bal amount: $66666.6  -->
            <DTASOF>20230901063405.495[-4:EDT]  <!-- Bal date: 09/01/2023, 06:34 am -->
        </AVAILBAL>

            </STMTRS>                  <!-- End statement response --> 
        </STMTTRNRS>                <!-- End of transaction -->

    </BANKMSGSRSV1>              <!-- End of response data -->
</OFX>

ofxparser.net which has a NuGet package is a great library for parsing OFX file

IIF

IIF format is fairly simple.

It contains lines starting with "!" to explain the columns of the corresponding data line. 

For example, the following file start with three lines !TRNS, !SPL,!ENDTRNS to explain the columns of the TRNS, SPL, and ENDTRNS lines following them respectively.

!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM CLEAR
!SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM CLEAR
!ENDTRNS
TRNS 15 DEPOSIT 7/16/1998 Checking 50 N
SPL 16 DEPOSIT 7/16/1998 Income Account Customer Class Name -50 236 N
ENDTRNS

 

 

This article was updated on 21:54:40 2024-06-03

You should also read: