2. PHPRunner General questions
  3. [SOLVED] import from bank
This topic is locked
[SOLVED]

import from bank

 		1/1/2025 11:19:08 PM
PHPRunner General questions
C
 chopperpilot author

Has anyone attempted to import bank transactions, pls share your experience. I need to import bank transactions daily and after getting the transaction match the transactions with existing data and allocate the associated values. but first is the import
A
 		AbriaCloud 1/2/2025

We have extensive knowledge on importing and synchronizing from multiple financial institutions. Unfortunately, each institution varies in how they implement their APIs and will provide varying levels of support for developers. There are third-party tools and subscriptions available to provide the link if API programming is too time consuming or beyond your comfort level.

In order to provide any feedback, I would need to know at least the name of the institution you have in mind.
C
 		Chris Whitehead 1/2/2025

I've been importing bank transactions using the csv exported from the bank rather than via an open banking API as I only do this once a month, I did have a copy and paste version until the bank changed the layout which made this useless.

I upload the csv and then process the line by line, to match the transactions I convert the line in into an md5 signature.

$signature = md5( json_encode( $csv_row ) );

I then store the signature in the table when I insert the record so I can check if the incoming signature and date keys already exist in the table, if it doesn't then I insert the record and ignore if it does.

You could use open banking to retrieve the transactions, here's a list of the APIs
https://ouelle.medium.com/open-banking-apis-list-for-creating-a-test-banking-app-7d696dac7103
C
 		chopperpilot author 1/2/2025

Thankyou Abriacloud and Chris. So nice of you guys. I use TD Bank and your help is greatly appreciated. you can reach me at 6472195303 or fpilot@renocon.ca
C
 		chopperpilot author 1/2/2025

https://authentication.td.com/uap-ui/?consumer=easyweb&locale=en_CA#/uap/login
C
 		Chris Whitehead 1/3/2025

You're welcome to use the same code that I use for synchronising the transactions. Here's the basics.

// get the signature from the csv row and check if exists
$import_signature = md5( json_encode( $csv_row ) );

// convert the date to the correct format for mysql
$date = strtotime( $csv_row['0'] );
$csv_row['0'] = date('Y-m-d H:i:s', $date);
$query = "SELECT * FROM bank_statements WHERE import_signature='".$import_signature."' AND tx_date='".$csv_row['0']."'";

//********** Check if specific record exists ************
$rs = DB::Query( $query );
$data = $rs->fetchAssoc();
if($data)
{
// if record exists then ignore and display a message
}
else
{
// remove the commas from the debit, credit and the balance columns
$csv_row['3'] = floatval(preg_replace('/[^\d.]/', '', $csv_row['3']));
$csv_row['4'] = floatval(preg_replace('/[^\d.]/', '', $csv_row['4']));
$csv_row['5'] = floatval(preg_replace('/[^\d.]/', '', $csv_row['5']));

// convert the credit column to a minus to insert in the table
$tx_value = !empty( $table_row['3'] ) ? $table_row['3'] : $table_row['4'] * -1;

// if record doesn't exist then insert
$fields = array(
'tx_date' => $csv_row['0'],
'tx_detail' => $csv_row['1'],
'tx_type' => $csv_row['2'],
'tx_value' => $tx_value,
'tx_balance'=> $csv_row['5'],
'bank_id' => $bank_id,// i have a couple of bank accounts
);
$fields['import_signature']= $import_signature;
//********** Insert a record into another table ************
DB::Insert("bank_statements", $fields );
}
A
 		AbriaCloud 1/3/2025

We tried to integrate with TD Bank a while back and they wanted developers to use Plaid (https://plaid.com) for integrations. Plaid was inordinately expensive for synchronizing checking account transactions. They were asking $12K/year (USD) just to maintain the API connection and we still had to integrate that code with the project.

In that case, the customer was interested in using Xero for their ledger. Xero was cheap and already had the integration with TD Bank. We were able to get around the problem by integrating with the Xero API instead of directly with the bank and that worked perfectly. Transactions would sync with Xero and we would sync Xero with their app.

I will check back with TD Bank and see if that situation changed.

C
 		chopperpilot author 1/21/2025

Thanks all.
A
 		AbriaCloud 1/23/2025

Forgot to update you. TD Bank is still a pain to work with. Nothing changed there.