Skip to main content

Julep: Expenditure Module: Import Guide for QuickBooks

Brooke Taylor
Updated

If you use QuickBooks for expenditures, you may also want to load them into Julep.  Using the Expenditure Import feature in Julep can make reporting easier.

 

Pulling the Vendor Contact List (if completed previously, move on to step 2):

When importing the QuickBooks’ Vendor Contact List for the first time, export the Vendor Contact List Report in QuickBooks and import it into Julep.  Note, you may need to update new vendors or vendor edits in the future for new vendors added over time.

  1. Click on the Reports menu on the left-hand side in QuickBooks.
  2. Go to the Expenses and Vendors area on screen.
  3. Click Vendor Contact List.
  4. Click Customize, a drop-down window will open, then click show more under Change Columns.
  5. Uncheck “Address”.
  6. Check “Street”, “City”, “State”, and “Zip” – Click
  7. Click the Excel button and then save the document.

NOTE:  There are different versions of QuickBooks. The directions in this guide pertain to QuickBooks online.  If you are using the desktop version, the directions will be similar, but may not be exact.

Prepping the Contact List for import:

  1. Within the Excel, remove the first 4 rows so that your header row is in row 1.
  2. Copy the Vendor Name column, and paste it as duplicate column in your file. Rename the column “Vendor Acct ID”.  NOTE: in future imports you will use this as your ID to match your expenditure records from QuickBooks to their correct Vendor in Julep.
  3. Move individuals into first and last name fields separate from the org names.
  4. Include the following required columns in your file:

Entity Type

This field is to match to the existing Payee types in Julep

Vendor Account ID*

This field is optional and should be used if the vendor already exists in Julep.  This is suggested to avoid duplicate issues

Org Name

Name of Vendor

First Name  

Use First Name field if vendor is and individual

Last Name

Use Last Name field if vendor is and individual
  1. Save the file.

Importing the Master Vendor List:

  1. To import, click on the mceclip0.png import icon in the Treasury Dashboard’s Navigation Bar.
  2. In Step 1: Upload File, click within the white box to choose a file from your computer or click and drag the file into the white box. Then, select the worksheet to be used for import from the file and click Next or Step 2: Import Type.
  3. In Step 2: Import Type, choose the import type of New Vendor and click Next or Step 3: Mapping.
  4. In Step 3: Mapping, the fields in the first column are the headers from the selected file. The dropdown fields in the second column are fields available in Julep. Use these drop-down options to map fields between the file and Julep. Make sure to map all the required fields and then click Next or Step 4: Validate and Import.mceclip0.png 
    • Use Auto Mapping and Julep will attempt to auto map the columns in the file to the columns in the database. If any fields are not auto mapped or are auto mapped incorrectly, you can manually update the mapped fields.
    • Save the mapping for future use by clicking Save. Choose Save to Selected Mapping (to select a previously saved mapping to overwrite) or Save to New Mapping (to create a brand-new saved mapping). Load Mapping loads previously saved mapping and Delete Mapping deletes any saved mapping. Click Reset to wipe out all mapped fields and start over.mceclip1.png
  5. In Step 4: Validate and Import, clickmceclip2.png . This step validates that all required fields are included and that the file’s format is correct.
    • If the import fails, a message will appear saying why the file was not validated and imported. Fix the listed errors, and then attempt the import again.
    • If successful, a confirmation message will appear confirming how many records were validated and imported. Another message will appear saying whether the import was successful with a preview of the output results.
    • Click mceclip3.pngto see the import’s results which you can save.

 

Pulling the Transaction List by Vendor:

  1. Click on the Reports menu on the left-hand side in QuickBooks.
  2. Go to the Expenses and Vendors area on screen.
  3. Click Transaction List by Vendor.
  4. Select the time frame of the reporting period.
  5. Click Customize. Adjust the selected export fields to only include: “Vendor”, “Date”, “Amount”, “Memo/Description”, “Type”.
  6. Scroll to the “Header/Footer” section and uncheck all boxes.
  7. Click RUN REPORT. NOTE: make sure to save report for future.
  8. Within the Excel, remove the first 4 rows so that your header row is in row 1.
  9. Review data. When ready click the Export/Excel button and then save the document.
  10. Most records in the Excel should have positive amounts. Negatives could be vendor refunds which should be loaded as Treasury Receipts in Julep.
  11. Delete the check payments for your regular bills but not for credit cards

REASON:  Credit card payments have multiple bills and purposes so the credit card payment made will be recorded as the live payment, but the ultimate vendors paid with that credit card will be reported as memo entries.

  1. The following are the fields you’ll want to have in your report:

 

Vendor Account ID

This field will be the Vendor Name field in your QuickBooks report.  This will act as a unique ID to match the vendor to its Julep record.  If the vendor does not exist in Julep yet, you will want to create the necessary Org Name, First Name, Last Name fields

Date

Include the date that the expenditure was made. 

Amount

The amount spent on this expense.

Description

This will most likely come from your QuickBooks report “Memo/Description” column.

Fund Code

Use this to indicate which Fund source the expenditure is going to come from.

Bank Account Code

If you are distinguishing which bank account the expenditure is being made from, then enter the correct code here. If not, then exclude this column and the import will default to “N/A” for this field.

Line Number

Enter the correct line number for the expense. 

Account

If you aren’t using a GL Code, then exclude this column and the system will automatically assign an “N/A” code. Otherwise, enter the breakdown of the expenditure by GL Code here.

 

  1. Save the file.

Ready to Import:

  1. Click themceclip5.png button in the Navigation Dashboard Bar on the Treasury Dashboard to import new Expenditures or Treasury Receipts.
  2. In Step 1: Upload File, click the white box to choose a file from your computer or click and drag your file into the white box. Then, select the worksheet that you want to import from your file and click Next or Step 2: Import Type.
  3. In Step 2: Import Type, choose the import type of New Transactions
  • Select the Transaction Type of Receipt or Disbursement and select the default Fund Code, Bank Acc Code, and Line No. from the drop-down menus. Then, click Next or Step 3: Mapping. For importing new payees, select the import type of New Vendor. For more information about importing new payees, see the Julep – Importing New Payees guide on our HelpDesk.
  1. In Step 3: Mapping, the fields in the first column are the headers from the selected file. The drop-down fields in the second column are fields available in the Julep database. Use these drop-down options to map fields between the file and Julep. Make sure to map all of the required fields and then click Next or Step 4: Validate and Import.

mceclip0.png

  • Use Auto Mapping and Julep will attempt to auto map the columns in the file to the columns in the database. If any fields are not auto mapped or are auto mapped incorrectly, you can manually update the mapped fields.
  • Save the mapping for future use by clicking Save. Choose Save to Selected Mapping (to select a previously saved mapping to overwrite) or Save to New Mapping (to create a brand-new saved mapping). Load Mapping loads previously saved mapping and Delete Mapping deletes any saved mapping. Click Reset to wipe out all mapped fields and start over.mceclip1.png

**Please note: Transactions imported under New Vendors or Payees do not need all required fields (only require Entity Type and Vendor Name). Address is not required but would have to be manually entered for each payee later on for reporting so we suggest including it in the original import.

  1. In Step 4: Validate and Import, click mceclip2.png. This step validates that all required fields are included and that the file’s format is correct.
    • If the import fails, a message will appear saying why the file was not validated and imported. Fix the listed errors, and then attempt the import again.
    • If successful, a confirmation message will appear confirming how many records were validated and imported. Another message will appear saying whether the import was successful with a preview of the output results.
    • Click mceclip3.pngto see the import’s results which you can save.

Entering and Linking Memo Transactions:

Used for credit card payments or staff reimbursements. The credit card payment or reimbursement is entered first. Details of the charges made on the card or by an individual are entered as memo entries after as a separate import. 

If the entry is a memo linked back to an Ultimate Vendor, then these additional fields required are:

Is Memo

Indicates whether an entry is a memo. More information on entering memos and linked transactions are below.

Linked Txn

Original payment’s Transaction ID (assigned by Julep).

If you are creating ultimate vendor entries for a payment you must do the following:

  1. Enter the payment to the individual or credit card company payment first
  2. Include the regular fields for Expenses plus the two additional fields above in the file
  3. To signify a memo entry, include a column for Is Memo
  4. Enter a “X” in the Is Memo field to signify it is a memo entry
  5. Enter the corresponding payment’s Linked Txn ID for each memo entry
  1. Import the ultimate vendors after payment entries have been made

 

To learn more about Ultimate Vendors, see the Julep – Expenditures – Ultimate Vendors guide on our HelpDesk.

Comments

0 comments

Please sign in to leave a comment.