Manual Journals

Xero:Import Manual Journal

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

Page Contents

• Transaction Import Features
• Template Example
• Manual Journal Structure in detail
• Multiple Manual Journals Posting into Xero
• Multiple Journals to Multiple Xero Companies
• Manual Journal Options
• Demo Videos
• Common Features
• Template Tags
• Related Example Templates included with PostTrans
• Online Transaction Training Course

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

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

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

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

Optional Detailed breakdown of Journal before 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

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

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

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

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

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 Setup.  In-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.

Download xeroNOM.xlsx
	Single Nominal Journal
	Single Nominal Journal with Contra Line
	Multiple Nominal Journals to Multiple Companies
	Multiple Nominal Journals