Menu

Manual Journals

Xero:Import Manual Journal

This type of PostTrans import, imports Manual Journal, which can be input manually, data from an ODBC connection or imported from CSV file.

Page Contents

Import Xero Manual Journal Features

  • Import Single Transaction, after data entry, or Multiple Journals
  • Customise manual data entry templates
  • Easy importation from CSV file
  • Post into Multiple Xero Companies Pro
  • Visual Validation of data as processed highlighting an error
  • Copy Paste Data
  • Use Excel formula/Macros or VBA to transform data/functionality
  • Double click on item Code to view related Xero Transactions
  • Optional Detailed Transaction Summary before posting
  • Can skip Zero values enabling quick entry template to be created
  • Cannot Post Transaction twice. Writes POSTED in row, with transaction Ref generated.
  • Search for Tax codes, Contacts, Product, Tracking code, etc., using our In-Cell Searching
  • Easily Add your own validation, using the THBefDisplayMessage tag

Template Structure for Xero Manual Journal Data

The template must have tag ‘xeroTransNom’ in the comment of cell A1 to instruct PostTrans that we are interested in importing Transactions, and that the sheet is a PostTrans Template. Pressing Import or Extract on a blank sheet will either open an existing example or create basic template for you to customise.

On-line Training video of how all this works 

Simple Manual Journal, single column, in Excel

Simple Manual Journal, single column, in Excel

Above is a screen shot of the Xero Manual Nominal Journal example template. Press Help, on the PostTrans toolbar to access example template, and select "Single Nominal Journal" to open it. Some of the columns can be removed, rearranged or extras added by using the Tags button to map Xero fields to the Excel Template.

These tags, listed in table below, can be easily added to a spreadsheet using the Tags button.

See Online Training for an explanation of this.

Example Xero Manual Journal in Excel

Simple Nominal in Excel

Simple Nominal in Excel

Any Column ending in a ^ denotes In-Cell Searching which means the user can easily search for associated codes. As seen in this example PostTrans writes back CODE, DESCRIPTION depending on the context of the data lookup. Everything after the comma is ignored. Therefore, if imported from a CSV file, or pasted in to sheet, column B would contain simple set of codes. See section below which explains more about In-Cell Searching

Simple Manual Journal, Credit/Debit column, in Excel

Simple Manual Journal, Credit/Debit column, in Excel

The above example separates the Credit/Debit figures into separate columns

Optional Detailed breakdown of Journal before posting into Xero

Optional Detailed breakdown of Journal before posting into Xero

Multiple Manual Journals Posting into Xero

Multiple Manual Journals Posting into Xero

Multiple Manual Journals Posting into Xero

Here we have moved the Narration cell from the transaction header to the lines. PostTrans first reads what is in the transaction header cells (if mapped), and then processes the lines. If the header field is defined in the lines then the value is overridden. Any change in Date or Narration will cause a new transaction to be started.

Blank/Duplicate values in cells are ignored.

Multiple Journals to Multiple Xero Companies

Multiple Journals to Multiple Xero Companies

Multiple Journals to Multiple Xero Companies

Here we have taken it one step further. We have added tag THBefSWCompany switches Xero Company before posting Journal. The Company codes are assigned by PostTrans. These can be selected using in-cell searching, or from Setup >> "Multi-Post" tab. Press Get Company List to get a list of the Companies. You must have given PostTrans authorisation to required companies when logging into Xero. The authorisation can be reset using the Force Login button on the first tab of the Setup window.

Xero Manual Journal Import Options

The header of the Journal has a section which contains Settings/Defaults, used to alter the PostTrans behaviour, and provide default values:

Settings and Defaults in template header

Settings and Defaults in template header

Option Description
Def Narration Default Line Description to Narration to journal lines, which can then be overitten
Show Cash If YES then Show On Cash Report
Acc Desc Default Line Description to Narration to journal lines, which can then be overitten
Amounts Are Tax is either Exclusive,NoTax
Status Default Status Value for whole Transaction if not listed in lines
Tracking1 Default Value for whole Transaction if not listed in lines
Tracking2 Default Value for whole Transaction if not listed in lines

Manual Nominal Journal Import has the following options, which are accessible by pressing the Setup button on the toolbar:

General Settings in Setup window

General Settings in Setup window

From this tab we can:

  • View Current Xero Company. Change it by pressing Change or Force Login to change the Xero authorisation of companies
  • Check for updates
  • View registration details. View Your Account and Billing takes you to your online account to purchase/cancel and manage subscription.

Switches in Setup window

Switches in Setup window

Option Description
Show Summary before posting transaction If OFF post all data in sheet without conformation. Caches transaction and post 25 Journals at a time for efficiency
if ABOVE OFF don't show warnings If above OFF does warn before posting, this turns that warning off
Turn off all Cell Lookups This option turns all in cell searching ON/OFF
Enforce Tracking entry 1 Enforce Tracking entry
Enforce Tracking entry 2 Enforce Tracking entry

Please see Online Training for how to customise PostTrans Templates for Xero.

Demonstration Video

 
 
   PostTrans for Xero Importing a Manual Journal
  0:30 Open Template from Examples
  1:10 Template Structure
  2:21 In-Cell Searching
  3:00 Edit data and add new Account
  3:00 Defining Columns
  3:20 Addin another column mapping to Xero
  5:50 Import new and changed records
  5:10 Extract single record
  5:30 Extract list of records
  6:00 Delete records if allowed
  6:40 Importing Multiple Manual Journals into Xero
  8:40 How to change the Xero Company
 
 

 
 
   PostTrans for Xero Importing a Manual Journal into Multiple Companies
  0:20 Open Example template
  0:30 Select a Xero Company manually for whole sheet
  1:50 Select a Xero Company within column
  2:00 Import 3 Journals into 2 different Xero Companies
 
 

Common Features

In-Cell Searching

Any Tag ending with ^ will have a In-cell search facility to look up associated values.  Use SPACE + TAB to see all values, or SPACE and TEXT to perform a text search on description. These are returned to the cell as "CODE, DESCRIPTION".  PostTrans only uses the CODE and ignores the comma and anything after it, thus data from a field would only need the CODE part of the data if from an external imported file.  

In-cell Searching can be turned off by removing the ^ from the end of the tag name, or name in first line of comment (Header cells) to disable in-cell searching for a single column, or header cell.  All in-cell searching can be turned off in SetupIn-cell searching.

Xero Data Cache

All associated Account, Tax, Currency, Contact and Product Item data needed to validate, or provide In-Cell Searching, is cached locally on you PC to enable PostTrans to deliver Optimal performance. This cache can be tailored to you own particular needs using the Cache button the tool bar.

Cursor Path

The path the cursor takes through the sheet, when entering data manually, can also be easily defined in Setup.  See Set Cursor Path

Importing the Data

Simply press the Import button to validate and import the data into Xero.  PostTrans will read the Header cells and then process the rows of data until TLAccCode column contains no data. The Xero Company imported into can be set in the Setup window.

PostTrans ONLY edits those fields listed on the sheet when importing, thus any existing values, on a record, are unaffected.   New records may have default values for certain fields.

Validation

Any values and associated codes will be invalid during import.   The cursor will be moved to highlight any problems in data.

POSTED Document Ref

After Posting a transaction, PostTrans will write "POSTED:" and the transaction reference number in column A (tag TLPosted).  Lines marked as "POSTED" are then ignored by PostTrans when re-posting. So, transactions cannot easily be posted twice. So the transaction cannot easily be posted twice.   The user would have to remove "POSTED" from column A in order to post the transaction a second time.  The user would have to remove "POSTED" from column A, to post the transaction a second time.

Confirmation before Posting Transaction

Just before posting the validated transaction, PostTrans will optionally show a transaction summary for the user to validate.   This can be turned off in Setup window.

File Import Button

This can be enabled in Setup to easily import CSV, or fixed length files, into sheet for importation.   Formula can also be added, or macros/VBA to manipulate this data before importation.   Import from CSV file.

Clear Button

The Clear button will clear all values on the sheet, according to the row just above the tag row, and copy down any formula.  Clear Transaction and copy down formula

Evaluation and License

This functionality will work with Xero Demo data without a license. A license is required to import into any other Xero Company. 
A subscription be purchased, and cancel on-line at any time very easily.   Pricing

What else can I do?

Other Xero Extract/Import types

Frequently Asked Questions?

Got another question? It may well be covered on our Frequently Asked Questions page.

PostTrans Tags which map to Xero

Not all of these tags/columns are needed. The table below lists the required and recommended:

Tag Name

Data Type

Description

Important Fields:

TLPosted

Text Req

Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row (Double Click to view Xero Journals)

Nominal Lines:

TLAccCode^

Text 10 EOF Req

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
    In Cell Searching values in table ACCCODE

TLDesc

Text 250

Desctription

TLTaxCode^

Text 100

Tax Code. Will fill in default if blank. Can be Xero Code or Tax name e.g. OUTPUT2 or 20% (VAT on Income)
    In Cell Searching values in table TAXCODE-NOM_TLACC

Value:

TLDebit^

Currency

Debit. Posttrans will ignore line if zero value in TLDebit and TLCredit
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLCredit^

Currency

Credit. Posttrans will ignore line if zero value in TLDebit and TLCredit
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLAmount^

Currency

Amount (used instead of Debit and Credit, thus +/- values in one column). Posttrans will ignore line if Zero Value in TLDebit and TLCredit
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

Additional Anaysis:

TLTracking1^

Text 100

Tracking code1 on the Line.
    In Cell Searching values in table TRACKING1

TLTracking2^

Text 100

Tracking code 2 on the Line.
    In Cell Searching values in table TRACKING2

Line Special Functions:

TLForceNewTrans

Yes/NoTF

If YES then force a new transaction

Header:

THNarration

Text 250 Head Chg

Journal Narration, if THDefNarration=YES then copied to each line

THDate

Date Head Chg

Journal Date, if not given today

THTaxType^

Text 60 Head

Tax is either Exclusive,NoTax

THStatus^

Text 60 Head

Default Status Value for whole Transaction if not listed in lines

THDefNarration

Yes/NoTF HeadOnly

Default Line Description to Narration to journal lines, which can then be overitten

THShowOnCash

Yes/NoTF HeadOnly

If YES then Show On Cash Report

THSetAccDesc

Yes/NoTF HeadOnly

IF YES then fill in the Account Description in TLDesc

Contra:

THContraAcc^

Text 8 HeadOnly

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
    In Cell Searching values in table ACCCODE

THContraDesc

Text 60 HeadOnly

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.

THContraTaxCode^

Text 100 Head

Contra Tax Code.
    In Cell Searching values in table TAXCODE-NOM_THACC

THContraTracking1^

Text 100 HeadOnly

Tracking code on the Header. Define Tracking Category in Setup >> Tracking
    In Cell Searching values in table TRACKING1

THContraTracking2^

Text 100 HeadOnly

Tracking code on the Header. Define Tracking Category in Setup >> Tracking
    In Cell Searching values in table TRACKING2

Analysis:

THDefTrack1^

Text 100 HeadOnly

Set Tracking for whole transaction unless overridden in Lines
    In Cell Searching values in table TRACKING1

THDefTrack2^

Text 100 HeadOnly

Set Tracking for whole transaction unless overridden in Lines
    In Cell Searching values in table TRACKING2

Transaction Update:

Header:Before Posting:

THBefSWCompany^

Text 6 Head Chg Pro

This will cause PostTrans to change company before posting if different from current Sage Line 50 Company. Thus can be user to post transactions into different companies, thus intercompany transactions are possible. It uses a 6 letter company code, which found by pressing the Select Company button in system setup.
Posting to multiple companies
    In Cell Searching values in table CompanySelect

TLForceBlankDesc

Yes/NoTF

If YES then force Blank Description

THBefDisplayMess

Text 60 HeadOnly

If contains value, then this text is displayed, and import cannot take place until this text if clear. Thus you can your own formula, macro or VBA code to stop import until a certain condition.
Validating Transaction before posting

THBefDisplayMessYN

Text 60 HeadOnly

If contains value, then this text is displayed. Asks Yes/No to continue
Validating Transaction before posting

Header - After Posting:

THAftProcessSheet

Text 60 HeadOnly Pro

After posting this sheet, switch to work book with this name, and post that. Allows back to back purchase orders, or multiple company transfers. PRO
Posting multiple sheets

THAftSwitchSheet

Text 60 HeadOnly Pro

After posting this sheet, switch to work book with this name PRO
Posting multiple sheets

Header - After Save:

THAftSavePATH

Text 60 HeadOnly Read

If THAftSavePATH and THAftSaveFilename specified, will save as PATH + ‘\YYYMM\’ + Filename + "_0000.xls". YYYMM can be overridden with value in THSaveSubDirectory cell.
more about saving

THAftSaveSubDir

Text 60 HeadOnly Read

See THAftSavePATH
more about saving

THAftSaveFilename

Text 60 HeadOnly Read

See THAftSavePATH. Example ‘ABAP01’ will save as ABAP01_0001.xls.
more about saving

Import File:

THImportFullName

Text 500 HeadOnly

Full PATH and Filename of the file just imported using the Import button. This is required if you set the directory to move file after posting transaction.
File Import instructions

THImportFileName

Text 500 HeadOnly

Filename of the file just imported using the Import button
File Import instructions


Req = Field is required    EOF = End of file, Stop when no more values    

Head  A header tag can be used in header section, before row 30, of the transaction template.  Or in the rows data of data, below row 30, of the template like a traditional CSV importer.

With data type Yes/No fields, if cell starts with 'y' or 'Y' is taken as TRUE

Where ^ denotes a In Cell Code Search, delete ^ at end of tag, to turn off code search

All Text fields are trimmed (spaces before) by default. Add ' to the end of the tag name to stop this.

.

Related Example Sheets

You can easily access the example sheets by pressing Help button, on the PostTrans Toolbar to access these related examples, or downloading them from the links below.

To use the template, with Sage 50, install PostTrans. Download
Trial with Sage demo company. Purchase a month subscription for Live Sage Company. Price
Watch the On-line Training course on how to use and customise the templates.

 

Single Nominal Journal

 

Single Nominal Journal with Contra Line

 

Multiple Nominal Journals to Multiple Companies

 

Multiple Nominal Journals