Learn how to create and edit QuickBooks journals directly from Excel.
This article covers the following:
- Getting Started
- Columns Explained
- Grouping of lines
- Editing Transactions
- Scenarios When This Template Could be Used
It is recommended to familiarise yourself with the following articles:
Important notes to be aware of when using this template:
Each row in the template represents a transaction line. Only those marked with a ‘Yes’ will be POSTed.
In the POST column, select ‘Yes’ if you want to post directly to Quickbooks. Select ‘Delete’ if you wish to delete the journals.
Since each row is a transaction the total Debit and total Credit amounts need to be equal.
You can upload a supporting working as an attachment for each Journal. To effect this, please select ‘Yes’ under Upload Sheet column and make sure that the respective supporting sheet name is identical to the JOURNAL NUMBER field in the template.
This template does not allow the use of Memos.
Mandatory fields must be filled in to successfully push a row to the accounting software - these are also marked with a white star in the template. The other columns are optional and can be used depending on what information needs to be posted/imported.
|Post?||Change to ‘Yes’ if you want to post the row details to your accounting software. Don’t forget to mark as ‘No’ for journals which are not required during this reporting period. There is NO NEED TO DELETE this entry … it may be required again during your next reporting period or for another Client.|
|Upload Sheet?||You can upload supporting workings as an attachment to each row. To effect this, please select ‘Yes’ under the Upload Sheet? column, then create a new sheet and make sure to write the sheet name in the Journal number column. In the newly created sheet, paste in any documentation or workings you need. This sheet alone, will be added as an attachment to your QuickBooks client.|
|Journal number||A unique Journal number has to be given to each journal. This journal reference controls how journal lines within the spreadsheet are grouped together and can be used again, but is unique to each journal (this is not to be confused with the separate System Journal reference automatically assigned by Xero).|
|Entry Date||This is the date that you would like your journal to be posted. This can be past, present or future.|
|Journal Description||A description for each journal is required…|
|Account||Assign an account to this transaction (these are downloaded from the selected Quickbooks Client).|
|Customer||Choose, if you would to, the customer related to this Journal.|
|Vendor||OR Choose a Vendor related to this Journal. This field, similar to Vendor, optional.|
|Location||Location may be selected if they are configured in the connected QuickBooks organisation.|
|Class||Class can also be selected. This is similar to Location .|
|Tax Rate||Choose the tax rate that needs to be applied to this transaction (these are downloaded from the selected QuickBooks organisation).|
|Tax Application||Choose between Sales Tax and Purchase Tax.|
|Currency||Choose the currency you would like to associate to the transaction from the dropdown.|
|Debit||Enter the debit value…|
|Credit||…and the credit value. The total debit lines and the credit lines for each Journal number must be equal to 0.|
Grouping of Lines
The template caters for creating transactions with multiple lines. When the following columns are identical, DataDear will automatically collate them as a single transaction with multiple lines - there is no limit (unless imposed by the accounting software) on the number of lines that can be used:
When grouping lines, it is recommended to put these under each other in the template.
Editing TransactionsThis template is a Post and Edit template, meaning it allows for transactions to be downloaded ( pulled from QuickBooks) , edited and posted back to the accounting software.
If you are not familiar with this process please go through the article below.
How do I push & edit data into the accounting software?
Scenarios When This Template Could be Used
Year end Adjustments
When closing off the financial year and posting the year end adjustments, this template can be very helpful as it will expedite the process of closing off P/L accounts as well as posting any Audit adjustments.
Bank interest postings
QuickBooks does not calculate Bank interest, this template can be setup once and amounts can be overwritten every month to post. Using this template will allow users to setup the journal entries once and post different amounts whenever bank interests need to be posted.
Accruals and prepayments adjustment.
The Manual journals template can also be used to post Accruals and Prepayments adjustments on a month by month basis or as needed.