You can use FinJinni to add general transactions to QuickBooks. These transactions can be created using an Excel spreadsheet or other program to create a data file that FinJinni will load. FinJinni will then write these transactions to QuickBooks.
This feature is only available if you have purchased the license for the FinJinni Importer advanced data loading feature. It is enabled in the trial versions so that you can preview it.
| For QuickBooks Desktop | For QuickBooks Online |
| Bill | Bill |
| Check | Purchase |
| CreditCardCharge | |
| CreditCardCredit | |
| CreditMemo | CreditMemo |
| Deposit | Deposit |
| Estimate | |
| Invoice | Invoice |
| PurchaseOrder | PurchaseOrder |
| RefundReceipt | |
| SalesOrder | |
| SalesReceipt | SalesReceipt |
| VendorCredit | VendorCredit |
Accounts, Classes, and Entities that are referenced by these transactions must already exist in QuickBooks. You can add Customers, Vendors, and Jobs/Projects to QuickBooks using a separate data file, described below, but these must be added prior to loading the transaction file.
FinJinni’s will load these transactions from CSV-format files that you can create with Excel. There are separate files for each QuickBooks company and you may have multiple files for one company. All files should be saved in the folder “documents”\My Templates\FinJinni, where “documents” is your normal documents folder.
The data files that FinJinni will load are named “companyid_Transaction_[suffix].csv”, where “companyid” is the company identifier (aka nick-name) that you assigned when you added the company to FinJinni and “suffix” is a value to uniquely identify the file if needed – you can pick anything you want for this, such as a date.
The FinJinni Excel Add-In can assist in saving these CSV-format data files from Excel worksheets. Once you create a worksheet with the columns described below, use the Upload button on the FinJinni ribbon bar in Excel to save the worksheet as a CSV file. You can also save it using the normal Excel menu – the FinJinni Upload button is a convenient shortcut.
There are a large number of supported data fields for transactions. Generally, the QuickBooks name assigned to a field is used. For a complete list of all the fields, refer to one of these sample transaction spreadsheets supplied with FinJinni:
Sample_ ImportData _QBD.xlsx -or- Sample_ ImportData _QBO.xlsx
Each transaction must have a “parent” row in the file and one or more detail rows. All line items for the transaction must have the same transaction date and reference number. Each line item occupies a separate row in the data file and all line items must be in sequence following the parent.
These files have at minimum the following columns and require a header with the column name in the first row:
- TxnType
The type of the transaction, from the list above.
- TxnDate
The date for the transaction.
- RefNumber
The transaction reference number. This must be unique for each transaction. For QuickBooks transactions that do not support a reference number (e.g. Deposits), this value is still required and is used to uniquely identify the transaction within FinJinni.
- Account
The account name or description (in the format “number – name”). This can also be the full name for an account (in the format “parent:child” used by QuickBooks). Any of these formats that uniquely identifies the account is sufficient.
- Amount
The amount for line items. For the parent transaction, QuickBooks will automatically calculate the total amount from these individual line amounts.
A few other commonly used fields are these. Some may be required for certain transactions.
- Description
The description (memo) for a line item.
- Memo
The memo field value for the transaction.
- Revised
Y to revise an existing QuickBooks transaction. N or blank otherwise.
Change values will not be automatically written back to QuickBooks after a transaction is initially added. If you set this field to “Y”, FinJinni will update the transaction in QuickBooks on the next refresh regardless.
Revised transaction cannot have new line items. If you need to add new line items, void or delete the transaction and create a new one.
- Voided
V to void an existing QuickBooks transaction (note: “V” not “Y” here). Otherwise leave blank. D to delete an existing transaction.
- Class
A class name. If you have parent & child classes, this must be the full name in the format “parent:child”.
- Customer
A customer, or full sub-customer name in the format “parent:child”. Projects or jobs are sub-customers and need their parent customer identified.
- Vendor
A vendor name.
Usage Notes:
- Transaction files are automatically loaded whenever a refresh is performed. FinJinni will detect whenever the file is changed (or a new file is added) and load the file.
- You must select the option “Enable Write-Back to QuickBooks” on the company setup screen in order to allow FinJinni to write data to your QuickBooks company.
- You can also check the status of all user-defined transactions from Excel using the FinJinni Query “User-Defined Transaction Postings” under the section “System Information Queries”.
Creating Multiple Company Data Files in Excel
You can use the FinJinni Upload function in Excel to create multiple data files from a single Excel worksheet. To use this feature in this way, add a Company_ID column as the first column of your worksheet and choose select “Multiple Companies & Fields” from the company drop-down on the upload screen. FinJinni will separate the rows into their appropriate company files.