From Spreadsheet to FINTRAC API: Building EFTR and LVCTR Templates Mapped to the JSON Schema

A spreadsheet export is source data, not a FINTRAC report. To turn it into an API submission:

1. Decide which records are reportable, and in which report type.
2. Group them into reports.
3. Split each record into the three blocks FINTRAC's JSON uses: report details, party definitions and transactions.
4. Convert values into the formats and codes the schema and validation rules accept.
5. Validate the result.
6. Have it approved.
7. Submit it through an enrolled API integration and record what FINTRAC sends back.

This guide works through that process for the Electronic Funds Transfer Report (EFTR) and the Large Virtual Currency Transaction Report (LVCTR). The field paths, types and patterns come from the published FINTRAC API JSON schema for each report and the matching validation rules, checked in October 2026. The examples are illustrations, not submittable payloads; each one is labelled.

If the business has not yet decided whether to report through the API at all, start with choosing between the API and the Web Reporting System.

What the FINTRAC API expects

FINTRAC's API report submission page (updated June 23, 2026) describes a system-to-system channel with two groups of endpoints, one for single reports and one for bulk files. Each supports creating, changing and deleting a report. A technical administrator enrols through the FINTRAC API portal and creates an application with secret keys. Each report type has its own JSON schema and its own validation rules page, and FINTRAC publishes a validation-message schema for responses.

The published technical limits are:

These are technical limits, not reporting thresholds. If a report would exceed the transaction limit, FINTRAC says to create a further report, link the reports by adding a suffix to the reporting entity report reference (for example -01, -02), and number the transactions incrementally across the linked reports. Actions at a different date and time belong in a new transaction, even if there are fewer than 50.

The EFTR and LVCTR schemas are separate documents with different structures. Each requires the same three top-level properties, reportDetails, definitions and transactions, and allows no others. What sits inside them differs.

Why one spreadsheet row is not one report

Payment platforms export one row per ledger event. FINTRAC's JSON is organized around reports, transactions, actions and parties. Keep these concepts separate in your data model:

Several rows can become one report. Suppose a customer sends three international transfers of CAD 4,000 within one 24-hour window. Your aggregation analysis may conclude they must be reported together. Those are three source rows, one report, and three entries in transactions[], with the aggregation basis recorded in reportDetails.twentyFourHourRule. Whether they aggregate depends on the applicable FINTRAC rules, not on the export layout; the 24-hour rule examples work through the analysis.

One row can also involve more parties than a flat sheet can hold. In FINTRAC's official EFTR sample B1, a single transfer involves:

• an individual requester
• the company the requester acts for
• that company's account
• a foreign beneficiary entity
• the reporting entity as initiator
• a foreign receiving bank

A sheet with one "sender" column and one "beneficiary" column cannot represent that reliably.

Design the source workbook before writing JSON

Before any code is written, a linked set of tables makes the mapping auditable. The structure below is an illustrative internal design, not a FINTRAC format. FINTRAC does not prescribe a spreadsheet layout, database or programming language.

A fictional extract from the transactions and actions sheets for one outgoing transfer:

Two of these values cannot go into JSON as they are. 8,200 is not an accepted amount string, and US Dollar is not a currency code. The sections below show the conversions.

Mapping spreadsheet fields to EFTR JSON

These paths come from the current EFTR schema (schema $id ending /eftr/v1/schema#, file dated June 24, 2024). The table is a representative selection, not an exhaustive list.

The amount in startingActions[].details.amount is the amount of that action, in the currency in which it was requested. It is not the CAD figure your system used to test the threshold, and it is not a fee. Threshold and exchange-rate decisions belong to the reportability analysis, which should be recorded before the JSON is built. The EFTR guide covers thresholds, initiation and final receipt.

Direction changes the structure, not just one code. In FINTRAC's initiation sample (B1), the reporting entity is the initiator and a foreign bank is the receiver. In its final receipt sample (C1), a foreign bank is the initiator, an intermediary appears in senders[], and the reporting entity is the receiver with its reportingEntityLocation.

EFTR example: partial illustration

Label: partial illustrative structure, adapted from FINTRAC's official sample B1. It is not a complete payload and cannot be submitted as is. The reportDetails block is omitted, and all names, numbers and references are fictional. For a complete document, use FINTRAC's official EFTR sample B1 and the other scenarios linked from the API page.

{

 "definitions": [

   { "typeCode": 2, "refId": "RE-NAME", "nameOfEntity": "Northbank Remit Inc." },

   {

     "typeCode": 3, "refId": "P-C55821",

     "givenName": "Amara", "surname": "Okafor",

     "addressTypeCode": 1,

     "address": { "typeCode": 1, "buildingNumber": "120", "streetAddress": "Example Avenue",

                  "city": "Mississauga", "provinceStateCode": "ON",

                  "postalZipCode": "L5B0A1", "countryCode": "CA" },

     "dateOfBirth": "1984-03-12", "occupation": "Pharmacist",

     "identifications": [ { "identifierTypeCode": 4, "number": "X0000-00000-00000",

                            "jurisdictionOfIssueCountryCode": "CA",

                            "jurisdictionOfIssueProvinceStateCode": "ON" } ]

   },

   {

     "typeCode": 4, "refId": "E-BEN-7731", "nameOfEntity": "Bayside Trading Ltd.",

     "addressTypeCode": 1,

     "address": { "typeCode": 1, "buildingNumber": "45", "streetAddress": "Sample Street",

                  "city": "Manila", "countryCode": "PH" },

     "registrationIncorporationIndicator": false,

     "registrationsIncorporations": [], "identifications": [], "authorizedPersons": []

   },

   {

     "typeCode": 8, "refId": "FI-RECV-01", "nameOfEntity": "Example Bank Philippines",

     "addressTypeCode": 1,

     "address": { "typeCode": 1, "buildingNumber": "1", "streetAddress": "Sample Plaza",

                  "city": "Manila", "countryCode": "PH" }

   }

 ],

 "transactions": [

   {

     "electronicFundsTransactionDetails": {

       "eftTypeCode": 1,

       "thresholdIndicator": true,

       "regulatoryExceptionIndicator": false,

       "dateTimeOfTransaction": "2026-09-15T14:32:00-04:00",

       "reportingEntityTransactionReference": "RMT-2026-0915-0042",

       "additionalPartiesSendingIndicator": false,

       "additionalPartiesEffectingSwiftIndicator": false,

       "additionalPaymentInformations": []

     },

     "initiator": { "typeCode": 2, "refId": "RE-NAME",

                    "details": { "reportingEntityLocation": { "id": "LOC-01" } } },

     "startingActions": [

       {

         "details": { "amount": "8200.00", "currencyCode": "USD",

                      "sourceOfFundsObtainedIndicator": false },

         "sourcesOfFunds": [],

         "requesters": [

           { "typeCode": 3, "refId": "P-C55821",

             "details": { "clientNumber": "C-55821", "accounts": [],

                          "otherAccountHoldersIndicator": false,

                          "onBehalfOfIndicator": false },

             "otherAccountHolders": [], "onBehalfOfs": [] }

         ]

       }

     ],

     "senders": [],

     "involvements": [],

     "completingActions": [

       {

         "details": { "involvementsIndicator": false },

         "beneficiaries": [

           { "typeCode": 4, "refId": "E-BEN-7731",

             "details": { "accounts": [ { "number": "PH-ACCT-000123", "holders": [] } ],

                          "onBehalfOfIndicator": false },

             "onBehalfOfs": [] }

         ],

         "involvements": []

       }

     ],

     "receiver": { "typeCode": 8, "refId": "FI-RECV-01",

                   "details": { "bicOrBeiCode": "EXMPPHM1" } }

   }

 ]

}

How the spreadsheet values became fields:

Several values were copied from the official sample's pattern and must be confirmed against FINTRAC's code lists for a real transfer. These are eftTypeCode, identifierTypeCode and the empty registration arrays for the beneficiary. The indicator sourceOfFundsObtainedIndicator: false is also fictional; whether source-of-funds information is needed depends on the facts. The payment-partner data guide covers which information a reporting entity should hold before filing.

Mapping spreadsheet fields to LVCTR JSON

The LVCTR schema ($id ending /lvctr/v1/schema#, dated June 24, 2024) is not a renamed EFTR. These are the differences that matter for mapping:

• There is no eftDirectionCode.
• Each transaction requires reportingEntityLocationId at the transaction level. EFTR places the location inside initiator or receiver.
• The person who conducts the transaction is a conductor, not a requester.
• Sources are sourcesOfVirtualCurrency[], not sourcesOfFunds[].
• Party definitions use typeCode 1 to 4 only. EFTR also allows 7 to 10.
• Aggregation codes differ. LVCTR uses 1 beneficiary, 2 conductor, 3 on behalf of and 4 not applicable (validation rule 50022). In EFTR, 2 is not used and 5 means requester.

Blockchain data rarely supplies everything the report needs. A transaction hash and a pair of addresses show that value moved. They do not identify who conducted the transaction, whether someone was acting on behalf of another person, or who the beneficiary is. Those come from your customer records. FINTRAC's LVCTR guidance asks for the sending address, the receiving address and the transaction identifier for each transaction. It also says to use the exchange rate the business sets in the normal course of business, under a documented process, because the Bank of Canada does not publish rates for virtual currency. Valuation and wallet questions are covered in the LVCTR guide.

LVCTR example: partial illustration

Label: partial illustrative structure built from the LVCTR schema. FINTRAC's API page lists no official LVCTR JSON sample, and this is not one. It omits reportDetails and most person details, uses fictional placeholder strings in place of real hashes and addresses, and cannot be submitted.

{
 "definitions": [
   { "typeCode": 3, "refId": "P-C90417", "givenName": "Daniel", "surname": "Tremblay",
     "identifications": [] }
 ],
 "transactions": [
   {
     "reportingEntityLocationId": "LOC-02",
     "virtualCurrencyTransactionDetails": {
       "thresholdIndicator": true,
       "dateTimeReceived": "2026-09-20T09:41:07-04:00",
       "ids": ["EXAMPLE-TX-HASH-NOT-REAL-0001"],
       "reportingEntityTransactionReference": "VC-IN-2026-0920-0007"
     },
     "startingActions": [
       {
         "details": {
           "amount": "0.50000000",
           "virtualCurrencyTypeCode": "BTC",
           "exchangeRate": "82500.00",
           "sendingVirtualCurrencyAddresses": ["EXAMPLE-SENDING-ADDRESS-NOT-REAL"],
           "sourceOfVirtualCurrencyIndicator": false,
           "conductorIndicator": true
         },
         "sourcesOfVirtualCurrency": [],
         "conductors": [
           { "typeCode": 3, "refId": "P-C90417",
             "details": { "clientOfReportingEntityIndicator": true,
                          "clientNumber": "C-90417", "onBehalfOfIndicator": false },
             "onBehalfOfs": [] }
         ]
       }
     ],
     "completingActions": [
       {
         "details": {
           "dispositionCode": 3,
           "amount": "41250.00",
           "currencyCode": "CAD",
           "receivingVirtualCurrencyAddresses": ["EXAMPLE-RECEIVING-ADDRESS-NOT-REAL"],
           "involvementIndicator": false,
           "beneficiaryIndicator": true
         },
         "involvements": [],
         "beneficiaries": [ { "typeCode": 3, "refId": "P-C90417" } ]
       }
     ]
   }
 ]
}


Values that need confirmation from real records and current code lists:

• virtualCurrencyTypeCode: FINTRAC's LVCTR form shows "Bitcoin/BTC" as an example, but use the published list.
• The exchange rate and fiat amount.
• The disposition code that matches what actually happened.
• The completed person details. The empty identifications array is accepted by the schema for this type, but may not satisfy the validation rules.
• Whether the customer is both conductor and beneficiary.

The same refId appears in two roles here, which is valid when one person conducts the transaction and benefits from the conversion.

Definitions and references

Both schemas define each party once in the definitions array and point to it from wherever the party appears. A definition carries typeCode and refId plus the party's data. A reference repeats the typeCode and refId, and adds role-specific details.

The level of detail is set by typeCode, and each position accepts only certain codes. In the EFTR schema:

• account holders accept 1 or 2 (name only)
• requesters and beneficiaries accept 3 or 4 (person or entity details)
• the initiator and receiver accept 0, 1, 2, 7 or 8

So the same real-world party can need more than one definition. FINTRAC's sample B1 shows this. Company C is defined twice:

• CompanyC (typeCode 2, name only), referenced as an account holder
• CompanyCDetails (typeCode 4, full details), referenced in both otherAccountHolders and onBehalfOfs

One definition is reused in two places, and one entity has two definitions at different levels of detail.

The published schemas check that each refId matches its pattern, ^[A-Za-z0-9-_]{1,50}$. They do not check that the refId exists in definitions. The business rules then validate the referenced data in context. For example, an entity's details must carry the same type code as the beneficiary reference (rule 145610), and an entity's name is required when thresholdIndicator is true (rule 145620). Run your own reference check before submission. The snippet below is an illustrative preliminary check, not a FINTRAC validator. It finds unresolved, duplicate and mismatched references, and nothing else:

def check_references(report):
   defs, problems = {}, []
   for d in report["definitions"]:
       if d["refId"] in defs:
           problems.append(f"duplicate refId {d['refId']}")
       defs[d["refId"]] = d["typeCode"]

   def walk(node, path):
       if isinstance(node, dict):
           if "refId" in node and "typeCode" in node and not path.startswith("definitions"):
               ref, code = node["refId"], node["typeCode"]
               if ref not in defs:
                   problems.append(f"unresolved {ref} at {path}")
               elif defs[ref] != code:
                   problems.append(f"typeCode mismatch for {ref} at {path}")
           for key, value in node.items():
               walk(value, f"{path}.{key}" if path else key)
       elif isinstance(node, list):
           for i, item in enumerate(node):
               walk(item, f"{path}[{i}]")

   walk(report, "")
   return problems


Unused definitions are worth flagging too. They usually mean a party was dropped from a role during mapping.

Report grouping and the 24-hour rule in the data model

Aggregation is decided before JSON generation. The data model has to preserve the decision.

• Grouping key. Store the key that produced each report: report type, the aggregation basis (for example the same requester, or the same beneficiary), the party key and the window start. Do not group by row order or calendar date.
• Window. periodStart and periodEnd are required unless a ministerial directive code is given, in which case they are not allowed (EFTR rules 140050 and 140051; LVCTR rules 50030 and 50031). Rules 140063 and 50043 reject an end date that is "not 24 hours from" the start. FINTRAC's sample B1 fills these fields with 00:00:00 and 23:59:59 on the same date. That reads as the first and last moments of one 24-hour window, the same way FINTRAC's 24-hour rule guidance describes a window as "9 am Monday to 8:59 am Tuesday". The rule text itself does not say how the end moment is counted. Follow the convention in the official sample, and confirm it with FINTRAC before production.
• Window choice. The window does not have to be a calendar day. The guidance says the reporting entity determines when its static window begins and ends, that the 24 hours must be consecutive, and that the times belong in its policies and procedures. The JSON records the window that policy produced; it does not define it.
• Type code. EFTR rule 140042 limits "not applicable" (4) to situations involving a single above-threshold transaction or a ministerial directive. An aggregated report needs the matching basis code.
• Separate logic per report type. EFTR and LVCTR use different aggregation codes and different party roles. Do not share one grouping function between them.
• Traceability. Keep a link from each transactions[] entry back to its source row, and from each report to its grouping key, so that a reviewer can rebuild the decision.
• Split reports. Where a report exceeds the API limits, apply FINTRAC's suffix convention and continue the transaction numbering across the linked reports.

Normalization rules

A mapping specification you can maintain

The table below is an illustrative internal control, not a FINTRAC-prescribed document. It shows the minimum fields worth recording for each mapped item.

Keep a header on the specification recording:

• the schema URL and file date in use
• the validation rules page date
• the mapping owner and the compliance reviewer
• the last review date
• links to test evidence
• a change history with an approval for each change

Compliance owns the meaning of each field, and development owns the transformation code. Neither should change a mapping alone.

Validate before submission

Treat validation as three separate layers. Passing one does not imply passing the next.

Layer 1: parsing. Is the document syntactically valid JSON? Catch encoding problems, trailing commas and unescaped characters here.

Layer 2: schema validation. Validate against the current official schema for that report type, using a validator that supports the JSON Schema draft the file declares (the EFTR schema declares draft-07). This layer checks structure, required properties, types, enumerations, string lengths and patterns. It also rejects properties the schema does not allow, since many objects set additionalProperties: false. JSON Schema can express some conditional logic, and FINTRAC's schemas use it. For example, if/then blocks require identifications when a definition's typeCode is 3. Most of FINTRAC's cross-field requirements are not in the schema files, though: indicators that must agree with their lists, fields that depend on disposition or direction, and code lists. Those are published separately as business validation rules. In these schemas, nothing checks that a refId resolves to a definition. A payload therefore has to be checked against both the schema and the rules.

Layer 3: FINTRAC business validation. The EFTR validation rules and LVCTR validation rules (both updated July 12, 2025) classify each rule as Presence, Content or Format, with an action of Reject or Warning. Verified examples:

• Uniqueness: a report reference that is not unique for the reporting entity is rejected (EFTR rule 140032, message 997).
• Cross-field consistency: onBehalfOfIndicator must agree with whether onBehalfOfs has entries (EFTR rule 146161, message 302).
• Conditional presence: the LVCTR completing-action currencyCode is required for some disposition codes and prohibited for others (rules 51980 and 51981).

A payload can pass all three technical layers and still be wrong: the wrong beneficiary, or a transfer that should have been aggregated. Technical validation does not establish that a report is accurate or complete. For handling messages that come back from FINTRAC, see common FINTRAC validation errors.

Worked example: one outgoing remittance from export to evidence

Hypothetical. A Canadian remittance MSB exports its daily payments to CSV. Row T-90042 is an outgoing international transfer: the customer requested USD 8,200 to a business beneficiary abroad. No other transactions aggregate with it.

The threshold decision comes first and sits outside the JSON. FINTRAC's EFT reporting guidance says to convert a foreign-currency amount using the exchange rate published by the Bank of Canada in effect at the time of the transaction. Using a fictional rate of 1.3800 CAD per USD, chosen only for this illustration: 8,200.00 × 1.3800 = CAD 11,316.00. That is at least CAD 10,000, so the transfer is reportable. The MSB records the rate, its source and the result in its threshold record. The JSON action still carries the original "8200.00" and "USD". The CAD figure is evidence for the decision, not a value in the starting action.

1. Source data. The transactions, actions and txn_parties rows shown earlier, plus party rows for the customer (C-55821), the beneficiary and the receiving bank.
2. References. The report is given EFT-20260915-0007, recorded in report_control before any JSON exists. The transaction keeps RMT-2026-0915-0042.
3. Grouping. One report with one transaction. aggregationTypeCode is 4 ("not applicable"), because this is a single above-threshold transaction, and the window covers the transaction date.
4. Paths. These are the paths in the EFTR mapping table, with eftDirectionCode 1, because the MSB initiated the transfer.
5. Transformations. The amount, currency and date are converted as shown in the EFTR illustration.
6. Party references. The customer becomes P-C55821 (typeCode 3) and is referenced as the requester. The beneficiary becomes E-BEN-7731 (typeCode 4). The receiving bank becomes FI-RECV-01 (typeCode 8), referenced as receiver. The MSB's name definition is referenced as initiator, with its location ID.
7. Local validation. The JSON parses. Schema validation runs against the official file. The reference check finds four references, four definitions, no orphans and no unused definitions. A second check confirms that the indicators match their lists.
8. Compliance review. A reviewer compares the payload with the source records and the threshold calculation, and approves it in the reporting log.
9. Submission status. The submission is logged with an attempt ID. The MSB records the response and does not mark the report "filed" until it has a definitive result.
10. Evidence. The MSB keeps:
    • the source rows and threshold calculation
    • the generated JSON
    • the mapping version
    • validation results
    • the approval
    • the FINTRAC response
    These are retained together under the report reference.

The example does not include a complete, validated payload. For one, use FINTRAC's official sample B1.

Responses, acknowledgements and duplicates

FINTRAC's validation-message schema and sample describe the bulk response:

• a status object (code, en, fr)
• counts of reports accepted with warnings, accepted without warnings, rejected and total
• an acknowledgements array, keyed by each report's reportingEntityReportReference

Each acknowledgement can carry one of two kinds of validationMessages:

• Schema messages, with instancePath, keyword and message
• Business-rule messages, with rule and an error object

The sample's status reads "Success with Errors": of seven reports, four were accepted without warnings, one was accepted with a warning, and two were rejected. A successful HTTP call therefore does not mean every report was accepted. Read the per-report results. FINTRAC's API overview page does not publish a separate response sample for single-report submissions, so confirm that format in the Swagger reference before building parsers around it.

Track each report through explicit states:

For each attempt, log:

• the internal report reference
• the attempt ID
• the request and response time stamps
• the status code
• the raw response
• the person responsible for follow-up

Duplicate prevention is an implementation control. FINTRAC's published material does not describe an idempotency key. What it does provide is the uniqueness rule on the report reference. Keep that reference fixed across retries. After a timeout, reconcile against FINTRAC's response or your submission records before resending, rather than regenerating the report under a new reference. When a submitted report needs to change, follow the report corrections guide rather than creating a fresh duplicate.

Keeping mappings current

• Track sources. FINTRAC's API page shows dates per file. In October 2026, the EFTR and LVCTR schemas were dated June 24, 2024, and their validation rules July 12, 2025, while the CDR files were updated in June 2026. Store each file's URL, date and a hash, and check for changes on a schedule.
• Validate against the version you mapped to, and record that version with every generated report.
• Treat a rules or code-list update as a change request. Assess its impact, update the mapping specification, and get approval.
• Regression-test. Keep a fixed set of fictional test reports covering initiation, final receipt, aggregated reports, entities, persons and LVCTR dispositions. Re-run them after every mapping or schema change.
• Protect production. Mapping changes reach production only after tests pass and compliance approves.
• Reconcile periodically. Compare submitted reports and acknowledgements against the population that should have been reported.

Pre-submission checklist

• Reportability and report type decided and recorded
• Current official schema and validation rules identified and noted
• Mapping version approved
• Source information complete, or gaps handled and documented without placeholder values
• Party definitions de-duplicated; every refId resolves with a matching typeCode
• Timestamps carry the correct offsets; amounts and currency codes in the required format
• Aggregation basis, window and grouping key recorded
• JSON parses; schema validation passed
• Business rules reviewed, including conditional fields and indicators
• Compliance approval recorded
• API enrolment and credentials confirmed for the reporting entity
• Per-report result captured; warnings and rejections assigned
• Evidence stored under the report reference

Frequently asked questions

Should we keep the source spreadsheet or only the JSON and acknowledgements?

Neither alone is enough, but not every export needs to be kept. The PCMLTFA record-keeping requirements apply to the applicable transaction records themselves, which normally live in the payment or ledger system, not in a temporary export. Under your retention policy, keep:

• those original records
• a traceable link from each source record to the report and transaction it fed
• the mapping and schema versions used
• the generated payload and FINTRAC's acknowledgement

If an export is the only place a reported value can be traced, keep that snapshot with the report evidence.

Can fictional test data be used for local validation?

Yes. Fictional reports are the right way to test parsing, schema validation and reference checks locally, and they keep personal information out of development environments. Do not send them to FINTRAC's production endpoint. The API overview page does not describe a test environment, so confirm the available testing options through the FINTRAC API portal documentation before planning end-to-end tests.

What if a customer identifier changes between the ledger and the reporting system?

Keep a dated cross-reference between the old and new IDs. Generate refId values from a stable internal party key rather than a source ID that can change. A refId only has to be consistent within one report, but clientNumber should reflect the identifier your records use, so that a FINTRAC query can be traced back to the customer.

Turning payment exports into accurate EFTR and LVCTR submissions is mostly a data-control problem: reliable mappings, checks before submission, review, and a record of what was sent and what came back. ComplyFactor offers FINTRAC reporting software that supports structured imports, reusable field mapping, validation checks, approval workflows and acknowledgement tracking. Reports can be submitted through the FINTRAC API where the reporting entity is enrolled.