Conduit Programming

Version française

Installation

FAQ

Configuration

File details

QIF Files

CSV Files

OFX Files

matirecsv

History

Links

e-mail me

Essentially as a way to understand the syncs between a Palm and a Windows PC, I wrote a conduit for MaTirelire.
MaTirelire (means MyPiggybank) is a bank account manager for the Palm OS.
A mailing list (in French) is available.

Pre-requisite

The 3.0.1 or later version of HotSync is required.
Previous versions of HotSync do not handle COM conduits.

Documentation about how to syncronise with the emulator can be found here.

To configure the Emulator for synchronization

Installation

Mode Functional
Syncronise the files Yes, Version 1.1 and later
Desktop overwrites handheld yes
Handheld overwrites desktop Yes
Do Nothing Yes

downloadPALM's COM Environment (1.02 Mo) if you do not have it already. This file is necessary for the conduit to work at all.

downloadConduit (1.47 Mo) including the VB environment.
OR
downloadConduit alone (54.7 Kb) if you already have the executing environment VB 6.0. As it is necessary to register the DLLs in the registry, the file enreg.bat will perform this operation.

downloadVersion 1.3.1 of the DLL (50.1 Kb) allows file deleting on PALM and PC.

All the files are compressed as ZIP archives. If you do not have the software to open them, you will find a trial version of WINZIP at www.winzip.com or a free clone at www.powerarchiver.com

For a new install, you need to install :

  1. PALM's COM environment
  2. Conduit (with or without VB)
  3. Conduit alone

You will then need to make HotSync aware of the conduit by running ONCE installeur.exe.

To update, you only need to install version 1.2.3 by copying the new DLL in the installation folder. It replaces the previous one.

The conduit has been tested with versions 3.0.1, 3.0.3, 3.1.0, 4.0, 4.0.0 of HotSync.

Configuration

screen copy

The options available in the HotSync action frame are clear enough and do not need further explanations.

The two following options will facilitate migration of the text files produced by the conduit to other applications.

Programme à lancer avant la HotSync
If you want a program to be run BEFORE the syncronisation between the PALM and the PC, set its path here. The program will be given as parameters the name and path of the MaTirelire.txt file.

Programme à lancer après la HotSync
Here you can set a path to a program that can be used AFTER syncronising the PALM and PC, ie. after the conduit has generated the text files. You can for example use the program ToQif to create .QIF files (usable by Money and Quicken).

If Détruire les anciens fichiers is checked, during sync, the new .qif files overwrite the old ones. If the option is unchecked, the existing files on the PC are sent to the trash before the new ones are generated by the synchronisation.

Synchronisation details

During sync, several files are created in the MaTirelire folder. This folder lies in your Sync folder. The default for this folder being C:\Palm\Username or, for Clié users C:\Program Files\Sony Handhelds\Username

The following files are generated:

Name Function
categories.txt List of accounts
Desc.txt Descriptions of operations
MaTirelire.txt List of all operations
Mode.txt Payment modes
Type.txt Types of operations

Details of the file categories.txt

Each line contains a record according to the pattern :

Name, identification, show.

Name is an alphanumeric string.
Identification is a number between 0 and n, where n is the maximum authorized.
Show is true if the Name must show, otherwise any other value.

Examples

Unfiled, 0,True
Citybank , 1, True
Amex , 2, True
Savings, 3, True

Details of the file Desc.txt

This file contains the descriptions (used for automatic filling), each on a separate line.

Example

Balance
Cash withdrawal
Cash deposit
Check deposit

Details of the file Mode.txt

This file contains the modes of payment each on a separate line.

Example

Check
Card

Details of the file Type.txt

This file contains the types of operations each on a separate line.

Example

Food
Withdrawal
Transports
Gas

Details of the file MaTirelire.txt

Each operation is on a separate line

Each line contains the following entries, separated by ;

  • Unique identifier of an existing record, or 0 for a new one
  • Category (account), the ID found in the file categorie.txt
  • Attribute, use the value 64 for added or modified entries, 128 for those to be deleted
  • Date following the pattern dd/mm/yyyy hh:mm
  • Amount, in cents
  • Checked 0-> no 1-> Checked 2-> Marqued 3-> Checked and marqued
  • Mode of payment, rank in the file Mode.txt otherwise 64. The first rank in the file Mode.txt having # 0
  • Type of operation, rank in the file Type.txt otherwise 64. The first rank in the file Type.txt having # 0
  • Check # otherwise blank
  • Value date following the pattern dd/mm/yyyy otherwise blank
  • Repeats, otherwise blank
  • Transfer, ID of the transferred operation otherwise blank
  • Description of the operation, to use more than one line, use only LF (ASCII code 10) as a separator and not the combination CR (ASCII 13) LF (ASCII 10)

Example

7102463;1; 64;01/12/2001 11:25:00;140000;1;;;;;;;salary
7102464;1; 128;01/12/2001 11:30:00;5000;1;;;;;;;Mistake
0;0; 64;05/12/2001 14:57:00;-6902;0;3;;;;;;Walmart

QIF File

screenshot

downloadDownload ToQIF (103 Kb)

This small program can be used to generate QIF files (one for each account existing on the PALM).
Its path must be indicated in Programme à lancer après la Hotsync in the configuration panel (see above). Just after the filename, options can be set.

Quicken and Money require that the dates be set in a specific (and sometimes different, according to version) format, so options are provided to force these different formats.
The following formats are available : french long (dd/mm/yyyy), french short (dd/mm/yy) and english (mm/dd/yy).
The default (no letter after the file name) is french long.
If the option s or S is present, dates will be generated in the french short format.
If the option m or M is present, dates will be generated in the english format.

In my example, the file categories.txt contains the following accounts :

Unfiled, 0,True
CityBank , 1,True
Amex , 2,True
Savings, 3,True

The program ToQif will then create the files :
Unfiled.qif
CityBank.qif
Amex.qif
Savings.qif

Example with no options
!Type:Bank
D20/04/2002
T1400
U1400
CX
PSalary
MSalary
^
D15/04/2002
T-15.00
U-15.00
C
NCard
PGas
MGas
LCar:Gas
^

Example with option m or M
!Type:Bank
D04/20/02
T1400
U1400
CX
PSalary
MSalary
^
D04/15/02
T-15.00
U-15.00
C
NCard
PGas
MGas
LCar:Gas
^

Example with option s or S
!Type:Bank
D20/04/02
T1400
U1400
CX
PSalary
MSalary
^
D15/04/02
T-15.00
U-15.00
C
NCard
PGas
MGas
LCar:Gas
^

The QIF files are created using the specifications of the QIF format. Hereafter is an extract of these specifications :

Field
Indicator
Explanation
DDate
TAmount
UAmount
CCleared status
NNum (check or reference number)
PPayee
LCategory (Category/Subcategory/Transfer/Class)
^End of the entry

CSV File

screenshot

downloadDownload ToCSV (105 Kb)

This small program can be used to generate CSV files (one for each account existing on the PALM).
Its path must be indicated in Programme à lancer après la Hotsync in the configuration panel (see above). Just after the filename, an option can be set to use , as the separator instead of the default ;

In my example, the file categories.txt contains the following accounts :

Unfiled, 0,True
CityBank , 1,True
Amex , 2,True
Savings, 3,True

The program ToCSV will then create the files :
Unfiled.csv
CityBank.csv
Amex.csv
Savings.csv

Example with no option
Date;Type;Payee;Category;Debit;Credit;C
20/04/2001;TRSFR;Salary;Salary;;1400;X
09/09/2001;Card;Gas;Car:Gas;21,80;;

Example using the , option
Date,Type,Payee,Category,Debit,Credit,C
20/04/2001,TRSFR,Salary,Salary,,1400,X
09/09/2001,Card,Gas,Car:Gas,21.80,,

OFX Files

Download ToOFX (8 ko)

This small program can be used to generate (one for each account existing on the PALM). Its path must be indicated in

Programme à lancer après la Hotsync

in the configuration panel (see above).

The ToOFX.ini file must be copied in the same directory as the MaTirelire.txt one

ToOFX.INI content

[General]
Dest=c:\quickenw\OFXData

[CCP]
Version = 102
TRNUID=1234567890
CURDEF=EUR
BANKID=45678
BRANCHID=13245
ACCTID=12345678901

The section General have the parameter dest the destination directory for the OFX files, in this sample it's the Quicken one

The section CCP is the account name in MaTirelire, the same one as in the Categorie.txt file

The following parameters must be the same in the program that must read the OFX files, for more detail, please have a look on the documentation of this product (Quicken, Money, ...)

Parameter Version is the version number of the OFX file

Parameter TRNUID is the agreemeent number

Parameter CURDEF is the currency to use

Parameter BANKID is the bank number, first part of the UID account number

Parameter BRANCHID is the bank office number, second part of the UID account number

Parameter ACCTID is the account number, last part of the UID account number without the key

In my sample, the file CATEGORIES.TXT contains the following accounts
Unfiled, 0,Vrai
CCP , 1,Vrai
BNP, 2,Vrai
PEL, 3,Vrai

The program ToOFX will then generate the files :
Unfiled.OFX
CCP.OFX
BNP.OFX
PEL.OFX

Sample of OFX file

OFXHEADER:100
DATA: OFXSGML
VERSION:102
SECURITY: NONE
ENCODING: USASCII
CHARSET:1252
COMPRESSION: NONE
OLDFILEUID: NONE
NEWFILEUID: NONE

<OFX>
<SIGNONMSGSRSV2>
<SONRS>
<STATUS><CODE>0<SEVERITY>INFO</STATUS>
<DTSERVER>20030421
<LANGUAGE>FRA
<COUNTRY>FRA
</SONRS>
</SIGNONMSGSRSV2>
<BANKMSGSRSV2>
<STMTTRNRS>
<TRNUID>1234567890
<STATUS><CODE>0<SEVERITY>INFO</STATUS>
<STMTRS>
<CURDEF>EUR
<BANKACCTFROM>
<BANKID>30041
<BRANCHID>13245
<ACCTID>12345678901
<ACCTTYPE2>checking
</BANKACCTFROM>
<BANKTRANLIST>
<DTSTART>19700101
<DTEND>20030421
<STMTTRN>
<TRNTYPE>CREDIT
<DTPOSTED>20011201
<DTUSER>20011201
<TRNAMT>1400.00
<FITID>7102463
<NAME>salaire
</STMTTRN>
<STMTTRN>
<TRNTYPE>CREDIT
<DTPOSTED>20011201
<DTUSER>20011201
<TRNAMT>50.00
<FITID>7102464
<NAME>Erreur
</STMTTRN>
</BANKTRANLIST>
<LEDGERBAL>
<BALAMT>1450
<DTASOF>20030421
</LEDGERBAL>
<AVAILBAL>
<BALAMT>1450
<DTASOF>20030421
</AVAILBAL>
</STMTRS>
</STMTTRNRS>
</BANKMSGSRSV2>
</OFX>

matirecsv

DownLoad matirecsv.pl

This tools if writen in Perl
Tu use this tool, the perl interpretor is required, it's possible to found it on Internet.
You need to key in he field "Launch program after hotsynch"
F:\perl\perl D:\Matirelire\matirecsv.pl -f
Where "F:\perl" is the directory where is installed perl, and "D:\Matirelire\" is the one where is the script.

matirecsv.pl v0.9.0 - CVS file formater for Matirelire HotSync Files
Copyright (c)2004 by Jiffax

./matirecsv.pl [-F FormatString] [-N AccountName] [-S Separator] [-L] -f MatireFile

-f MatireFile: complete name (with all path) of Matirelire conduit MaTirelire.txt generated file -F FormatString: IADdMmPOTNVRtLn
-N AccountName: Name of an account, ar all if not given
-S Seperator: caracter separator. Defaut is ','
-L: don't use log (log.txt). Default is logging

Format String detail. Each Letter stand for a field of MatireFile line.
Put the letters in the order of your choice (repetitions allowed).
This order will be the order used in the target csv file.
Default: JMLnOnnT

I: Record ID
A: Attribute
D: Date (format DD:MM:YY hh:mm:ss)
d: Date (format DD:MM:YY)
J: Date (format DD)
M: Amount (+ and - in 2 separated columns, each figure positive)
m: Amount (+ and - in same column)
P: Valide / pointe ...
O: Mode of operation
T: Type
N: Check number
V: Value date
R: Repetition
t: Transfer
L: Label (text description of the operation)
n: null (empty field)

Log file name is matirecsv.log and is located either in $TEMP , $TMP or C:\ directory

Conduit history

Version Changes
1.3.2
  • Deleted category with record don't freeze any more the copy
1.3.1
  • Multi lingual version
  • Marqued record does no more lost this property
1.2.3
  • The program launched after sync is launched after creation of the file MaTirelire.txt on the desktop hard drive and not before.
1.2.2
  • Records can be deleted on Handheld and Desktop if the corresponding option is checked on the handheld.
  • The types, modes and descriptions are truncated at the authorized maximum.
1.2.1
  • Files are transfered to the Trash only if the option is checked on the Configuration Screen.
  • No error message if one of the text files is missing when sync is launched
1.2.0
  • Corrects problems in the sync between Handheld and Desktop.
  • It is not possible anymore to overflow the maxima in the notes, desc and types
  • Non-displayable characters are removed
  • Possibility of launching programs before and after syncing the data base. These programs will receive as parameters the name and path of the file MaTirelire.txt
  • Data are sorted before being written in the Handheld
  • Before sync, the files that will be overwritten are sent to the Trash instead of being simply deleted
1.1.0
  • Allows sync between Desktop and Handheld
1.0.1
  • Corrects the problem of -300 amounts being converted to -492 in the MaTirelire software
1.0.0
  • Original version

ToQIF History

Version Change
1.0.0.7
  • Using num check <100
1.0.0.6
  • Using num check
1.0.0.5
  • Possibility to choose your own QIF caption
  • Using a dictionary to translate type os operation between MaTirelire and the QIF file
  • The characters '>', '<', '?', '*' are no more filtered in check order
1.0.0.4
  • Filtering of characters in descriptions no longer removes accented characters.
1.0.0.3
  • Multi-lines are filtered in the descriptions
  • Option s or S permits to choose the short or long format for the date.
  • The problem of invalid page fault in the TOQIF.EXE module should be fixed.
1.0.0.2
  • Possibility to chose date format between french and english.
1.0.0.1
  • Recompiled original version
1.0.0.0
  • Original version

ToCSV history

Version Change
1.0.0.4
  • Filtering of characters in descriptions no longer removes accented characters.
1.0.0.3
  • Multi-lines are filtered in the descriptions
1.0.0.2
  • Possibility to choose the delimiter between columns. The default value is ; but it is possible to choose any character.
1.0.0.1
  • Recompiled original version
1.0.0.0
  • Original version

toOFX history

Version Change
1.0.0.0
  • Original version

Links

MaTirelire The website of the bank accounts management software.

The software's mailing list

PALM's official website



Ma Tirelire Conduit - Copyright © 2002

Translation: Gilles Richard

e-mail me ! e-mail the author

stats visitors since 20 April 2002