Import Journal Entries - Drag N Drop 

If you have standard journal entries that you need to make each month, an Excel Drag n Drop file can be used to import the distribution lines. Simply change the dates and amounts in the Excel file, then import into the system. You may also setup one workbook with monthly worksheets.

IMPORTANT NOTES

  1. The drag-n-drop feature can process Password Protected Excel files. If the .xls or .xlsx spreadsheet is password protected, Excel must be installed on the computer/server. Spreadsheets without a password do not require Excel installation on the computer/server.
  2. IFC Code usage in Journal Entries became functional in Financials Version 16.190.4.10 - 05/08/2020.  IFC should not be used in earlier versions. 

Setup Excel Template File

  1. Create an Excel worksheet with the following column headings in Row 1.
    1. Column A:  Account
    2. Column B:  IFC (Optional) Horizon Financials Version 16.190.4.10 or later is required for this feature
    3. Column C:  Memo
    4. Column D:  Date
    5. Column E:  Debit
    6. Column F:  Credit
    7. Column G:  Source (Optional)
    8. Column H:  Reference (Optional)
    9. Column I: Alloc (Optional)
    10. The import file needs each column listed above even if you won't use them. The column can be blank or hidden in the worksheet.
  2. Starting in Row 2, enter one line for each debit and credit item for the journal entry
    1. Every row should contain a line for the Journal Entry.
    2. Do not leave any blank rows.
    3. Do not put notes or calculations in rows between entries; add in Column L
  3. Column A Account must be filled in. Enter your GL account number with the separators, i.e., 1-1111-1-00
  4. Column C Memo: type the name of your GL account number, i.e., Cash
  5. Column D Date: type the date you want the entry recorded to the general ledger
  6. Column E Debit: if the line is a debit, enter the amount
  7. Column F Credit: if the line is a credit, enter the amount
  8. The IFC, Source, Reference, and Alloc columns are Optional.
  9. Calculating Debit and/or Credit Amounts
    1. You can enter a formula to calculate the Column E Debit/Column F Credit amounts.
    2. The displayed answer will be imported to the journal entry
    3. If you are calculating the entries in other Excel worksheets that do not match this format, you can copy and paste the figures from that worksheet into a template you save for Drag-N-Drop. You should use the Paste Special command and paste Values Only.
  10. If you want to add additional notes for each line, In Column L, add a column header NOTES. Add all documentation in a column with a header; the header name can be anything you want it to be, i.e., notes, documentation, totals, etc.
  11. If you want to add columns to calculate debit/credit totals, percentages, etc., enter this information after Column J
  12. Make sure any additional information or calculations are entered after Column L. Error messages will be displayed immediately after the JV entry information.
  13. Make sure columns have a header name as the system will overwrite columns with no header.
  14. Maintaining Monthly Import History
    1. You can setup one workbook with a worksheet for each month to keep the history.
    2. When you prepare the import file, move the current month to the first tab. 
    3. The Drag-n-Drop process only imports information from the first sheet. 
  15. SAVE the file to your computer/network as you normally would. 

Prepare Import File

  1. Open your template file
  2. Enter/update the dates and amounts
  3. Make sure the total of the debit column equals the credit column
    1. You can setup a formula after Column J to calculate the total of Column E Debit/Column F Credit amounts.
  4. If you have one workbook with a worksheet for each month, move the current month to the first worksheet.
    1. The Drag-n-Drop process only imports information from the first worksheet. 
    2. The worksheet tab names would typically be in order March, Feb, Jan, etc.
  5. Make sure any additional information or calculations are entered after Column L. 
  6. SAVE the file as you normally would
  7. CLOSE the file.
    1. This is very important.
    2. If the file is open, the import process may not work and you will get an error.

Import Journal Entry

NAVIGATION: ACTIVITIES menu > Journal Entry

  1. Enter the Description: type a description for the journal entry (up to 30 characters in length), i.e., February 2024 Payroll
  2. Open the File Explorer
  3. Navigate to the location on your computer/network where you saved your import file.
  4. Resize the File Explorer window and the Horizon program window so that you can see the unopened Excel file and the Journal Entry screen at the same time.
  5. Drag the Excel file onto the Journal Entry screen and Drop it on the Journal Entry screen.
    1. Click and hold on the unopened Excel file name
    2. Drag your mouse to the journal entry screen
    3. You should see a “+” sign indicating that you can drop the file.
    4. Let go of the mouse button
  6. The grid will update with the information from your Excel file
  7. If there is an error, NO data will be saved in the journal entry. You need to correct the error in the file and repeat the drag and drop.
  8. Resolving Errors
    1. If there are any errors in the spreadsheet, you will receive the error message "Errors found - please refer to spreadsheet".
    2. Click OK to close the error message.
    3. Open your spreadsheet from your computer/network
    4. In the first column with no header (typically column H or I), an error message will save in the rows in which an error was detected.
    5. Typical errors would be wrong account number, invalid date, etc.
    6. Correct each row that has an error listed.
    7. SAVE the file
    8. CLOSE the file
    9. Repeat the import steps again
  9. Make sure the entry is in balance.
  10. Click SAVE
  11. Follow the normal posting procedure; see General Ledger - Journal Entry

Having trouble resizing your windows? You may also leave both windows full screen, start with the folder containing your Excel file. Left click the file and hold while dragging it down to the Start bar to the Horizon Financials button. This will cause Horizon Financials to maximize and you may drag the file to the journal voucher screen.

Sample DND File

Drag N Drop JV.xls

4/2024

NEED MORE HELP?  Submit a ticket to the Help Desk or call (814) 535-7810 or (800) 889-3388