cancel
Showing results for 
Search instead for 
Did you mean: 
Frequent Explorer **

HOW TO INCLUDE BANK TRANSACTIONS THAT ARE NOT SHOWING IN THE BANK ANYMORE FOR BEING TOO OLD INTO QUICKBOOKS

My bank is only showing transactions for 2 years and I have 3 years of operations. I just bought the Quickbooks desktop version and I would to have everything in the system. However, I cannot import all transactions because the bank is not showing them. I have the bank statements and I can access to them in the bank page, but only that. Do I have to manually input all data? If so, how?

7 Comments
Senior Explorer ***

Re: HOW TO INCLUDE BANK TRANSACTIONS THAT ARE NOT SHOWING IN THE BANK ANYMORE FOR BEING TOO OLD INTO QUICKBOOKS

I am not sure which bank you are using, but each bank has different criteria on the length of time it holds information for download, have detailed information available and/or show bank statements online. 

 

You can surely key enter what is not available on the bank's website if you in fact have access to the bank statements, but I have to wonder why you want to recreate almost 3 years worth of data in QuickBooks.  If your tax returns have been filed for 2017 and prior, I would recommend working on only 2018.  You can begin by entering beginning balances for the balance sheet accounts as of 1/1/2018.  The journal entry would be to debit or credit each balance sheet account with the balance as of 1/1/2018 and use "opening balance equity" as the offsetting account.  The only accounts that you can not do this with is accounts payable and accounts receivable.  These accounts you must set each customer/vendor up with an opening balance and use the "opening balance equity" account as the offsetting entry.  This can be done through a journal entry for each customer/vendor or can be done by setting up an invoice for the customer and using an item that is associated with the "opening balance equity" account.  The vendors you would enter a bill and use the "opening balance equity" account as the expense on the bill.    

Highlighted
Anonymous
Not applicable

Re: HOW TO INCLUDE BANK TRANSACTIONS THAT ARE NOT SHOWING IN THE BANK ANYMORE FOR BEING TOO OLD I...

I'm pleased to see you today, May17,

 

I can add a bit more on how to import bank transactions into QuickBooks Desktop. 

 

In addition to @aj4fluentbiz's answer, you can also use the IIF toolkit to bring your transactions into QuickBooks Desktop. In your case, you'll only need to copy all the information manually from your statements, then map it correctly in the IIF file.

 

To know more about the standard IIF format and headers, please feel free to check out this article: IIF Overview: Import Kit, Sample Files, and Headers

 

Once you have the file ready, you can now start the transfer. Here's how to import an IIF file:

  1. In QuickBooks, sign-in as the company file Admin.
  2. Ensure you are in Single User mode.
  3. Click the File menu.
  4. Select Utilities.
  5. Choose Import then IIF Files.
  6. Locate your .IIF file and highlight it then select Open.
  7. Select OK on the pop up message confirming your data has been imported.

That should circumvent the issue with your banking feeds, May17.

 

Please let me know if there's any way I can help you with QuickBooks. I'm here to assist you anytime.

Community Contributor *

Re: HOW TO INCLUDE BANK TRANSACTIONS THAT ARE NOT SHOWING IN THE BANK ANYMORE FOR BEING TOO OLD INTO QUICKBOOKS

Unfortunately, yes. It has to be manually entered if you cannot download anymore.

Established Community Backer ***

Re: HOW TO INCLUDE BANK TRANSACTIONS THAT ARE NOT SHOWING IN THE BANK ANYMORE FOR BEING TOO OLD INTO QUICKBOOKS

Your bank Statement Dates are not your data entry dates; that is the date the Bank processed something. You issued checks and made deposits on actual dates that are your perspective, and not the date the bank processed it. Example:

You issue a check to someone Nov 2017, and they cashed it in Jan 2018. That is your 2017 business expense for purposes of business tax reporting. The Reconciliation date is the statement from Jan 2018, but that is not the expense date.

Community Contributor **

Re: HOW TO INCLUDE BANK TRANSACTIONS THAT ARE NOT SHOWING IN THE BANK ANYMORE FOR BEING TOO OLD I...

Hello, Thanks for the information. I am a neophyte QB user, helping someone out with their business paperwork, as his bookkeeper moved out of state and he needs his P&L done for 2018. I have some familiarity with QB but not the details and subtleties.

 

I want to enter several month's worth of BoA transactions from the statements (which I now have loaded into Excel).

 

I downloaded the IIF kit that defines the header and transaction format, however there are no sample examples, ie exactly how is a line of data formatted for a given transaction; like paying the phone bill (Telephone Account) from the check register (Checking Account). If that detail was provided, the theory would be much easier to understand, since there are 41 fields in each transaction, what do I do with the other 37 fields per transaction?

 

Can you provide such an example for a given transaction for me?

Thank you.

Frank

 

example:

4/13/2018,VERIZON WIRELESS Bill Payment,($350.00),Telephone:Wireless

QuickBooks Team

Re: HOW TO INCLUDE BANK TRANSACTIONS THAT ARE NOT SHOWING IN THE BANK ANYMORE FOR BEING TOO OLD I...

I can show you an example transaction, Franksi.

 

On the article my colleague @Anonymous provided, the file headers lines begin with TRNS, SPL, and ENDTRNS. These cells are equivalent to one transaction.

 

Please check my screenshots below.

 

 

 

Additionally, if your bank supports WebConnect, you can use this feature to download the bank transactions to QuickBooks. Check this article for more information: Import web connect (.qbo) files.

 

You can always get back to this thread if you need more help. Have a nice day!

 

Community Contributor **

Re: HOW TO INCLUDE BANK TRANSACTIONS THAT ARE NOT SHOWING IN THE BANK ANYMORE FOR BEING TOO OLD I...

Christine Mae,  Thank you for the illustration.  It is for an Accounts Payable transaction.  What I need is a Check Register (Bank) check written to a Payee, Account, Memo, Payment, Deposit (I assume the reconcile and balance will be managed by QB during it's subsequent internal processing steps).  Since the structure between your illustration of an Accounts Payable transaction and the Check Register are different, how exactly is this structure represented by the following I have loaded from the Bank Statement in Excel? (The 1st line is the Excel column identifiers that I layed-out/"matched" from the QB Check Register layout/format.  I also tried to layout the data under the column identifiers to properly represent the field locations, skipping columns when appropriate between the TRNS and SPL layout formats)

 

A               B               C                          D                                    E                      F                   G                H 

Date       Number   Payee                   Account                         Memo            Payment      Deposit     Bal

               Type

4/6/18 ,   2745,   Jim's Pump Srvc,   Concrete:Pumping,    172 Hillside,    -350.00

4/8/18,    Transfer,                          ,    Owner's Cap:Dep,      Cash Deposit,                ,  1,800.00

 

 

I don't want to goof up the QB database by importing the data incorrectly, so here is my best guess from the TRNS and SPL formats of what it could be.  Can you please validate/correct it for me?

A               B         C        D             E                                F                                G                      H          I      

TRNS     292     Bank   4/6/18    Bank of America     Check Register                               -350.00

SPL        293     Bank   4/6/18    Jim'd Pump Svc,      Concrete:Pumping 172 Hillside                 350.00

ENDTRNS 

TRNS     294    Bank    4/8/18    Bank of America      Check Register                                           1800.00

SPL         295    Bank    4/8/18    Owner's Cap:Dep   Cash Deposit                                -1800.00

ENDTRNS 

 

Thank you.

Frank

 

Need to get in touch?

Contact us