Using RT To Track My Finances

I use RT to automatically track my finances, by using the free Plaid API I am able to pull in transactions from my bank account automatically and create tickets in RT.

First I start by loading an RT group that has each of my users who are using my RT to track their finances:

my $financegroup = RT::Group->new( RT->SystemUser ); my ($ret, $msg) = $financegroup->LoadUserDefinedGroup( “Finance” ); unless ( $ret ) { RT->Logger->error( “Could not load Finance group: $msg” ); return; }

Then I map the RT fields to the Plaid API fields that I would like to populate on my finance tickets:

my %financefields = ( 'Amount Due' => 'amount', 'Paid on' => 'date', 'Category' => 'category', 'Transaction ID' => 'transactionid', 'Vendor' => 'name', 'Location' => 'location' );

I do some date calculations that I won't include here for sake of keeping this post short, once I have my date range of transactions I am looking for I can start looping over my users:

my $members = $financegroup->UserMembersObj( Recursively => 0 ); while ( my $member = $members->Next ) { unless ( $member->FirstCustomFieldValue( 'Plaid access token' ) ) { RT->Logger->error( $member->EmailAddress.” does not have a Plaid access token, skipping” ); next; } my $payload = { clientid => 'XXXX', secret => 'XXXX', accesstoken => $member->FirstCustomFieldValue( 'Plaid access token' ), startdate => $startdate, enddate => $enddate, }; my $encodeddata = encodeutf8(encodejson($payload)); my $request = POST 'https://development.plaid.com/transactions/get', 'Content-Type' => 'application/json', Content => $encoded_data; my $response = $ua->request( $request );

my $res = fromjson($response->decodedcontent); if ( $res->{errorcode} ) { RT->Logger->error( $res->{errormessage} ); }

In the above code block you can see that I am querying the Plaid transactions API over a date range for my user. Each user record has a custom field “Plaid access token” which is how we only grab that users transactions.

Now its time to get to the heart of this script and create some tickets from the transactions I found:

my @transactions = $res->{'transactions'} ? @{$res->{'transactions'}} : ();

foreach my $transaction ( @transactions ) { if ( $transaction->{'pending'} eq 'true' ) { RT->Logger->debug( “Skipping $transaction->{transactionid} as it is pending” ); next; } my $tickets = RT::Tickets->new( RT->SystemUser ); $tickets->FromSQL( “Queue = 'Finance' AND 'CF.{Transaction ID}' = '$transaction->{transactionid}'” );

if ( $tickets->Count ) { RT->Logger->error( “Found duplicate transaction: $transaction->{transactionid}, skipping sync.” ); next; } else { my $ticket = RT::Ticket->new( RT->SystemUser ); RT->Logger->debug( “Creating new ticket for tranaction: $transaction->{transactionid}” );

my ($ret, $msg) = $ticket->Create( Queue => 'Finance', Subject => “$transaction->{name} charged “.$transaction->{amount}*-1, Owner => $member->PrincipalId, ); if ( $ret ) { ($ret, $msg) = $ticket->Load( $ret ); RT->Logger->error( “Could not load new ticket: $msg” ) unless $ret;

($ret, $msg) = $ticket->SetStatus('paid'); RT->Logger->error( “Could not set status to paid: $msg” ) unless $ret;

foreach my $customfield ( keys %financefields ) { if ( $customfield eq 'Amount Due' ) { ($ret, $msg) = $ticket->AddCustomFieldValue( Field => 'Amount due', Value => “\$“.$transaction->{'amount'}*-1 ); RT->Logger->error( “Could not set amount due: $msg” ) unless $ret; } else { my @content = ref $transaction->{$financefields{$customfield}} eq 'ARRAY' ? @{$transaction->{$financefields{$customfield}}} : ($transaction->{$financefields{$customfield}}); foreach my $content ( @content ) { ($ret, $msg) = $ticket->AddCustomFieldValue( Field => $customfield, Value => $content ); RT->Logger->error( “Could not set custom field $custom_field: $msg” ) unless $ret; } } } }

Now that we have some data we can create reports like this! financial planner screenshot