Crimson: Expenditure Module: Import Guide for QuickBooks

If you use QuickBooks for expenditures, you may also want to load them into Crimson.  Crimson allows for FEC reporting info that QuickBooks does not.  For example, line numbers, cycle to date aggregates, memo linking, etc.  Using the Expenditure Import feature in Crimson 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 Crimson.  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 Crimson.
  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

ORG=Organization, IND=Individual, CAN = Candidate, CCM = Candidate Campaign Committee, COM = Committee, PAC = Political Action Committee, PTY = Party Organization

Vendor Account ID*

This field is optional and should be used if the vendor already exists in Crimson.  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 Crimson. Use these drop-down options to map fields between the file and Crimson. Make sure to map all the required fields and then click Next or Step 4: Validate and Import.mceclip1.png 
    • Use Auto Mapping and Crimson 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.mceclip2.png
  5. In Step 4: Validate and Import, clickmceclip3.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 mceclip4.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 Crimson.
  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 Crimson record.  If the vendor does not exist in Crimson yet, you will want to create the necessary Org Name, First Name, Last Name fields

Date

Include the date that the expenditure was made. This will be the date that shows up on the FEC report.

Amount

The amount spent on this expense.

FEC Description

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

Fund Code

The election cycle and period. Examples: P2024 (primary, 2024), G2024 (general, 2024). **This is for categorization in the database only, there is a separate field for Election CD to display this on the FEC report.

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. **There’s a list of the line numbers in the Crimson help desk titled “FEC Line #’s by FEC Reports”.

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 themceclip0.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 Crimson – 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 Crimson database. Use these drop-down options to map fields between the file and Crimson. Make sure to map all of the required fields and then click Next or Step 4: Validate and Import.

mceclip1.png

  • Use Auto Mapping and Crimson 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.mceclip2.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 FEC reporting so we suggest including it in the original import.

  1. In Step 4: Validate and Import, click mceclip3.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 mceclip4.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 Crimson).

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 Crimson – Expenditures – Ultimate Vendors guide on our HelpDesk.

Brooke Taylor -
Have more questions? Submit a request

Comments

Powered by Zendesk