Statement templates

Statements of transactions may be downloaded in delimited text format (CSV) and imported into your favourite program.

Templates to download

CSV Format

Format of the downloaded CSV file
The following table describes the field format with the type, length, number of decimal points and a description of the field.

Caisse, Folio, PCA, Date, Sequence, Description, Cheque, Withdrawal, Deposit, , , , , Balance
Caisse, Folio, RS, Date, Sequence, Description, , Withdrawal, Deposit, , , , , Balance
Caisse, Folio, TS, Date, Sequence, Description, , Withdrawal, Deposit, , , , , Balance
Caisse, Folio, CS, Date, Sequence, Description, , Withdrawal, Deposit, , , , , Balance
Caisse, Folio, LN, Date, Sequence, Description, , , , Interest, Capital paid, , Reimbursement, Balance
Caisse, Folio, LC, Date, Sequence, Description, , , , Interest, , Advance, Reimbursement, Balance
VISA #### #### #### ####, , , Date, Sequence, Description, , , , , , Advance, Reimbursement

Field formats
The following table provides information on field formats, namely the type, length, decimal places and field descriptions.

Field
Type
Length
Decimal
Description
Institution
C
25
No
Abbreviated name of caisse or VISA
Folio
C
7
No
Folio number
Type_account
C
5
No
Type of account: PCA, RS, TS, LN, LC, CS
Date
C
10
No
Format: YYYY/MM/DD
Sequence
N
3
0
Sequential number for sorting
Description
C
90
No
Description of transactions
Cheque
N
8
0
Cheque number (PCA)
Withdrawal
N
13
2
Withdrawal amount (PCA,RS,TS,CS)
Deposit
N
13
2
Deposit amount (PCA,RS,TS,CS)
Interest
N
13
2
Interest (LN,LC)
Capital paid
N
13
2
Capital paid (LN)
Advance
N
13
2
Cash advance (LC, VISA)
Reimbursement
N
13
2
Reimbursement (LN,LC, VISA)
Balance
N
13
2
Balance

Remarks:

  • The fields of a character ("C") type are placed between quotation marks ("").
  • The fields of a numerical type ("N") are not placed between quotation marks.
  • Absent fields are marked by "".
  • Decimals are set apart by a period (.).
  • Type of account corresponds to personal chequing, regular savings, etc.
  • The sequential number numbers the transactions for a caisse, folio, account and date.
  • The order of sorting or the sorting key is made up of the fields: caisse, folio, account, date, sequence.

Example of transactions:

"C.P. Ste-Edith","27656","PCA","1997/06/16",1,"Withdrawal at counter",125.75,"","","","","",678.65
"C.P. Ste-Edith","27656","PCA","1997/06/17",1,"Deposit at counter","",654.87,"","","","",2774.65
"VISA 4540 1234 1234 1234","","","1997/06/17",1,"Suzy Shier","","","","",124.66,"",2774.65

Templates to download

We have designed templates (models) into which you will be able to insert your first downloaded file. This ensures that you will have a good definition of the data in the software program of your choice. Therefore, you will not have to worry about correctly defining the width of columns in your spreadsheet or the right type of data in your database.

The following templates accept this format:

You may download the template of your choice by clicking on the name of the software that you like to use from the previous list.

Since we strive to satisfy your needs, we ask that you tell us the name of the software program that you use if it does not accept the previously mentioned templates.

Using templates
You must first download the template which corresponds to the software you use. Then, you can download your "Statement of Transactions" in a CSV format.

We suggest that you always keep a previous version of your working document. To do so, before starting to import any data, save your file under another name. That way, if you make a mistake, you won't lose all the information.

Lotus 1-2-3, here's how to proceed: (detailed procedure)
1. From the 'File' menu, select 'Open'.
2. Specify the downloaded CSV file containing your statement.
3. If you do not see your CSV file, type *.* for file name so that Lotus displays all files regardless of their extension.
4. Make sure that the file type is 'TEXT'.
5. Click on the button 'IMPORT' to activate the Import Wizard.
6. Every separator indicates the beginning of a new column.
7. The type of data must be set at 'Delineated'.
8. Click on the button 'OK'.
9. Select all data (from columns A to N) and click on 'Copy'.
10. Open the template.
11. Place the cursor after the last line in Column A.
12. Click on 'Paste'.
13. 'Save' the Template.

Microsoft Excel, here's how to proceed: (detailed procedure)
1. From the 'Data' menu, select 'Import External Data / Import Data'.
2. Specify the downloaded CSV file containing your statement.
3. If you do not see your CSV file, type *.* for file name so that Excel displays all files regardless of their extension.
4. Click on the button 'IMPORT' to activate the Import Wizard.
5. The type of data must be set at 'Delineated'.
6. Click on the button marked 'NEXT'.
7. The separator must be the comma, and the text indicator, quotation marks.
8. It is not necessary to change the format of the columns.
9. Click on the button 'END'.
10. Select all the data (from Columns A to N) and click on 'Copy'.
11. Open the Template.
12. Place your cursor just after the last line in Column A.
13. Click on 'Paste'.
14. 'Save' the Template.

Microsoft Access, here's how to proceed: (detailed procedure)
1. Open the Template.
2. From the 'File' menu, choose the option 'Outside data' and 'import'.
3. Specify the name of the downloaded CSV file.
4. If you do not see your CSV file, indicate *.* as file name so that Access displays all files regardless of their extension.
5. The type of data must be set at 'Delineated'.
6. Click on button 'IMPORT' to activate the Import Wizard.
7. The type of data must be set at 'Delineated'.
8. Click on the button marked 'NEXT'.
9. The field separator must be the comma.
10. The text separator must be quotation marks.
11. Click on the button 'ADVANCED'.
12. The period is the decimal symbol.
13. Click on 'OK'.
14. Click on 'NEXT'.
15. The data will be saved in an existing table called 'Transactions'.
16. Click on 'NEXT'.
17. Confirm the importing of data by clicking on 'END'.

Remarks concerning Access:

  • A query called 'Liste opérations' allows you to see your operation correctly sorted according to the appropriate sorting keys.
  • Another query called 'Opérations par compte' allows you to specify the account for which you want to see the list of operations.
  • A report function called 'Liste opérations' allows you to obtain a ready-to-print report.

Borland dBase and other compatible software programs:

  • The use of templates with these programs varies a great deal.
  • You cannot directly use the dBase importing facilities because the command APPEND FROM does not operate correctly with a CSV file.
  • You may write a dBase program which will read it with FREAD() and, after having extracted each field, will insert a record with APPEND BLANK and initialize all the fields there.
  • Make sure you are in the date mode with centuries (i.e., set century on).

Money working for people

Les grands prix Québécois de la qualité - Grand Prix 2007