Transaction CSV download

  Last updated: 

 

This topic describes how to download transaction data as Comma Separated Values (CSV) by submitting a HTTPS POST query string. By implementing the CSV download functionality outlined here, your system can be configured to process requests on your behalf, allowing you to automate the process of downloading the records required.

  CSV files of transaction data can also be downloaded by signing in to Portal or MyST and using the Transaction Search page.

  The maximum number of records that can be downloaded is 75,000.

If you have greater than 75,000 records, we recommend requesting smaller time-frames and performing multiple sequential requests

 

Process overview

This feature allows you to download details of requests processed on your account that meet your specified criteria. You can choose the fields that are returned, so you only download the information you need.

Example use cases:

  • Download information on all transactions processed on a single day.
  • Download the names of all customers that have made purchases for greater than £999 in the last month.
  • Download the transaction references of suspended transactions in a given time-frame.

The following is an example of a POST for requesting a transaction download CSV report:

wget --output-document=output.csv --user=example@example.com --ask-password --post-data="sitereferences=test_site12345&startdate=2017-01-01&enddate=2017-01-31&settlestatuss=100&optionalfields=errorcode&optionalfields=currencyiso3a&optionalfields=authcode&optionalfields=orderreference&optionalfields=paymenttypedescription&optionalfields=settlestatus&optionalfields=settlemainamount&optionalfields=settleduedate&optionalfields=customercountryiso2a" https://myst.securetrading.net/auto/transactions/transactionsearch

 

The request above consists of four main parts:

1. Basic Access Authentication

You will need to provide a Trust Payments API username with sufficient user privileges to perform the download for the given site reference(s).

--user=example@example.com --ask-password

The CSV file can be downloaded using a HTTPS POST query string with Basic Access Authentication. Basic Access Authentication is a method for a web browser client program to provide a username and password when processing a request.

 

Supported user roles

You can use your Webservices username and password to download transaction records and reports. If you do not already have a Webservices username, you can create a new API user using Portal or MyST.

Alternatively, you can create a new Extra transaction download user role, which has access to additional acquirer fee information. (Acquirer fee info is only available for transactions processed with a participating acquiring bank. Contact our Support Team for further information)

  We also support two additional user roles for CSV downloads. These are “Transaction download” and “Report download”, for downloading transaction records and reports respectively. To set up users with these roles, please contact our Support Team.

 

Authorisation header

A username and password, separated by a colon, then base64 encoded must be included in the authorisation header when performing the request. Most URL libraries will construct the basic authentication header automatically, but if you need to perform this manually, please follow this example:

Username: csvdownload@example.com
Password: pa55word
Separated by a colon: csvdownload@example.com:pa55word
Base64 encode: Y3N2ZG93bmxvYWRAZXhhbXBsZS5jb206cGE1NXdvcmQ=

Therefore, the authorisation header should be:

Authorization: Basic Y3N2ZG93bmxvYWRAZXhhbXBsZS5jb206cGE1NXdvcmQ=
2. Filters

Filters can be included in the request so that only records meeting certain criteria are returned.

sitereferences=test_site12345&startdate=2017-01-01&enddate=2017-01-31&settlestatuss=100

By default, all transactions within the date range for the specified site reference(s) will be returned in the download. Filters can be applied to the download so only certain records are returned. In the example above, the following filters have been applied:

  • Site reference: test_site12345
  • Transaction authorisation date: 1st January 2017 – 31st January 2017
  • Settle status: 100

Multiple values for the same filters can be included in the call separated by an “&”.

For example, for downloading e-commerce and MOTO transactions, the accounttypedescriptions filter can be included twice with two different values:

accounttypedescriptions=ECOM&accounttypedescriptions=MOTO

 

Required filters

The following fields must be included in the POST data:

Filter name Comment
sitereferences The site reference/s to download data from.
startdate The start date for the specified records (in the format YYYY-MM-DD).
enddate The end date for the specified records (in the format YYYY-MM-DD).

Data can be downloaded for multiple site references by including additional site references fields in the call separated by an “&”.

For example, if transactions are needed for site1 and site2, the call would include the following:

sitereferences=site1&sitereferences=site2

  Please note that even if downloading transactions for one site reference, the field must always be called sitereferences.

 

Optional filters

Useful filters

The following is a list of the most frequently-used filters by our merchants:
(Contact our Support Team to enquire about availability of additional filters)

Filter name Comment
accounttypedescriptions

Filter by account type, e.g. “ECOM” for e-commerce.

You can view the account types enabled on your account in Portal or MyST, by navigating to your site details page and clicking “Accounts”.

Click here for a list of account types.

currencyiso3as

Filter by currency.

Click here for a list of currency codes.

errorcodes

Filter by error codes.

Click here for a list of error codes.

paymenttypedescriptions Filter by payment type. e.g. “VISA”,  “MASTERCARD” etc.
requesttypedescriptions

Type of request, for example “AUTH”, “REFUND” etc.

Click here for a list of request types.

settlestatuss

Filter by status. e.g. “100” for all settled transactions.

Click here for further information on settlement.

  Note that to search by settlestatus, the field must be called settlestatuss.

 

Filter by time

Use the following filters to download data on transactions within a specified range of time:

Filter name Comment
starttimehour To find transactions between HH:mm:00 and HH:mm:59
*Required if end time specified
starttimeminute To find transactions between HH:mm:00 and HH:mm:59
* Required if starttimehour specified
endtimehour To find transactions between HH:mm:00 and HH:mm:59
* Required if start time specified
endtimeminute To find transactions between HH:mm:00 and HH:mm:59
* Required if endtimehour specified

For example, for downloading transactions between 13:30:00 and 14:29:59:

starttimehour=13&starttimeminute=30&endtimehour=14&endtimeminute=29

 

Date type

By default, dates and times specified are filtered based on when the transaction was initially processed. However, you can explicitly specify the type of date/time that is filtered by including the following additional filter:

Filter name Comment
datetypes Specify “settledtimestamp” to filter by date/time transactions were settled.

Specify “transactionstartedtimestamp” to filter by date/time requests were processed.

  If your system is processing a high volume of transactions, we recommend downloading CSV files of transactions on an hourly basis (instead of daily), for optimum performance and to reduce the size of the files downloaded. Your system can submit a request every hour, specifying the time filters as described above, to ensure only transactions processed in the last hour are included.

3. Display preferences

This is where you can specify the data fields to be returned.

As a minimum, all transaction download CSVs will contain the transaction reference for each record returned.

Example output:

transactionreference
13-2-81000
13-28-1
13-28-10
13-28-11

You can specify additional fields to be returned in the transaction download, as shown in the example below:

optionalfields=errorcode&optionalfields=currencyiso3a&optionalfields=authcode&optionalfields=orderreference&optionalfields=paymenttypedescription&optionalfields=settlestatus&optionalfields=settlemainamount&optionalfields=settleduedate&optionalfields=customercountryiso2a

The example above includes the following optional fields:

  • Error code
  • Currency
  • Auth code
  • Order reference
  • Payment type
  • Settle status
  • Settle main amount
  • Settle due date
  • Customer country

The output would contain these optional fields. The field names specified in the request are shown on the first line of the CSV output, in the same order:

transactionreference,errorcode,currencyiso3a,authcode,orderreference,paymenttypedescription,settlestatus,settlemainamount,settleduedate,customercountryiso2a
23-9-1,0,GBP,TEST,20161,VISA,100,10.99,2017-01-03,GB
23-9-2,0,GBP,TEST,20162,MASTERCARD,100,11.98,2017-01-10,GB
23-9-3,0,GBP,TEST,20163,MASTERCARD,100,10.99,2017-01-11,GB
23-9-4,0,GBP,TEST,20164,MASTERCARD,100,10.99,2017-01-16,GB

  If a requested optional field has no value, an empty string will be returned in the CSV.

 

Full list of optional fields

The following sections list fields that can be included in the output, if specified in the request:

 

Transaction details

Field name Comment
requesttypedescription Type of request performed (e.g. “AUTH”, “REFUND”).
transactionreference Trust Payments unique transaction reference.
parenttransactionreference Transaction reference of parent transaction. For example, in the case of refunds, this would be the reference to the transaction that has been refunded.
accounttypedescription

Account type, e.g. “ECOM” for e-commerce.

You can view the account types enabled on your account in Portal or MyST, by navigating to your site details page and clicking “Accounts”.

Click here for a list of account types.

baseamount Transaction in base amount. For example £10 will be 1000
mainamount Transaction in main amount. For example £10 will be 10.00
errorcode

Error code for the transaction.

Click here for a list of error codes.

currencyiso3a

Currency of transaction.

Click here for a list of currency codes.

authcode Transaction authorisation code.
customerip Customer IP.
fraudrating Transaction fraud rating.
orderreference Merchant’s order reference.

 

Payment details

Field name Comment
paymenttypedescription Type of payment, for example “VISA”, “MASTERCARD”.
maskedpan Masked card number.
expirydate Card expiry date.

 

Settlement details

Field name Comment
settlestatus

The current transaction status.

Click here for further information on settlement.

settlemainamount Amount due to settle/settled.
settleduedate Date due to settle/settled.

 

Security response

The response of the AVS and Security Code Checks checks performed during authorisation. This can be one of the following values:

  • 0 = DATA NOT GIVEN
  • 1 = DATA NOT CHECKED
  • 2 = DATA MATCH
  • 4 = DATA NOT MATCHED
Field name Comment
securityresponsesecuritycode Result of security code check.
securityresponseaddress Result of address check.
securityresponsepostcode Result of postcode check.

 

Billing details

Field name Comment
billingprefixname Name prefix. For example, “Mr, Mrs, Miss”.
billingfirstname First Name.
billingmiddlename Middle Name.
billinglastname Last Name.
billingsuffixname Name suffix. For example, “BSc, PhD”.
billingpremise First line of the address.
billingstreet Address Street.
billingtown Address Town.
billingcounty Address County.
billingemail Email address.
billingcountryiso2a

Address Country.

Click here for a list of country codes.

billingpostcode Address Postcode.
billingtelephones Telephone Numbers.

 

Delivery details

Field name Comment
customerprefixname Name prefix. For example, “Mr, Mrs, Miss”.
customerfirstname First Name.
customermiddlename Middle Name.
customerlastname Last Name.
customersuffixname Name suffix. For example, “BSc, PhD”.
customerpremise First line of the address.
customerstreet Address Street.
customertown Address Town.
customercounty Address County.
customeremail Email address.
customercountryiso2a

Address Country.

Click here for a list of country codes.

customerpostcode Address Postcode.
customertelephones Telephone Numbers.

 

Merchant details

Field name Comment
merchantname Merchant name associated with site reference.
tid The Terminal ID used to process the transaction. This is accredited to your merchant number when we setup your account in our systems.

 

Acquirer fee details

The following fields can only be returned when the following requirements are met:

  • These fields will only be populated when transactions are processed with a supported acquirer. Contact our Support Team for further information.
  • The user role Extra transaction download must be used to perform the download (these fields cannot be returned with user role Webservices).
Field name Comment
acquirerassessmentfeetotal The scheme fees charged by Visa and Mastercard.
acquirercalculatedpayment The calculated net amount for the given period.
acquirercrossborderfees Cross border fee charges from Mastercard in relation to the card scheme fees, as part of the IC++ pricing model.
acquirermpifeetotal The interchange fees charged by the Card Issuer.
acquirerpricingcategory Indicator of the card type (whether it is a consumer, premium or commercial card being used by the cardholder).
acquirerregion Indicator of whether the transaction takes place in a domestic, intra-regional or inter-regional environment.
acquirertranfees The transaction fees charged by the Acquirer.

 

4. Transaction download URL

Download the CSV from the following URL:

https://myst.securetrading.net/auto/transactions/transactionsearch

  Use of this URL is unaffected by the migration from MyST to Portal
Please continue to process requests using the MyST URL shown above.

 

Output

Providing your request is successful, the CSV output has the following structure:

transactionreference,errorcode,currencyiso3a,authcode,orderreference,paymenttypedescription,settlestatus,settlemainamount,settleduedate,customercountryiso2a
23-9-1,0,GBP,TEST,20161,VISA,100,10.99,2017-01-03,GB
23-9-2,0,GBP,TEST,20162,MASTERCARD,100,11.98,2017-01-10,GB
23-9-3,0,GBP,TEST,20163,MASTERCARD,100,10.99,2017-01-11,GB
23-9-4,0,GBP,TEST,20164,MASTERCARD,100,10.99,2017-01-16,GB
23-9-5,0,GBP,TEST,20165,DELTA,100,16.98,2017-01-26,GB
23-9-7,0,GBP,TEST,20166,AMEX,100,10.99,2017-01-29,GB
Was this article helpful?
0 out of 1 found this helpful