Learn more
 

In this article

Related articles

Reconciling Authorize.net Transactions with QuickBooks in PowerShell


The CData Cmdlets for Authorize.Net and QuickBooks offer live access to their respective data sources from within PowerShell, making it easy to automate regular tasks like transaction reconciliation. This article walks through using the CData Cmdlets in PowerShell to reconcile transactions between Authorize.Net and QuickBooks.

Connecting to Authorize.Net

To connect to Authorize.Net, the following connection properties are required. You can obtain these values on the Security Settings -> General Settings page after logging into your Merchant Account.

  • UseSandbox: The Authorize.Net API to be used to process transactions. If you are using a production account, this property can be left blank. If you are using a developer test account, set this to 'TRUE'.
  • LoginID: The API login Id associated with your payment gateway account. This property is used to authenticate that you are authorized to submit website transactions. Note that this value is not the same as the login Id that you use to log in to the Merchant Interface.
  • TransactionKey: The transaction key associated with your payment gateway account. This property is used to authenticate that you are authorized to submit website transactions.

After obtaining the needed connection properties, accessing QuickBooks data in PowerShell consists of three basic steps:

  1. Install the module.

    Install-Module AuthorizeNetCmdlets

  2. Connect to Authorize.Net:

    $authorizenet = Connect-AuthNet  -LoginId $LoginId -TransactionKey $TransactionKey

    If this is the first time you are using the cmdlets, you will be asked to license the installation.
    Enter 'TRIAL' to activate a free, 30-day trial.

  3. Retrieve the data from the Batches table based on the period start and end dates.

    $batches =  Select-AuthNet -Connection $authorizenet -Table "SettledBatchList" -Where "SettlementTime >= '$AuthNetStartDate' AND SettlementTime <= '$AuthNetEndDate'"

Connecting to QuickBooks

When you are connecting to a local QuickBooks instance, you do not need to set any connection properties.

Requests are made to QuickBooks through the Remote Connector. The Remote Connector runs on the same machine as QuickBooks and accepts connections through a lightweight, embedded Web server. The server supports SSL/TLS, enabling users to connect securely from remote machines.

The first time you connect, you will need to authorize the Remote Connector with QuickBooks. See the "Getting Started" chapter of the help documentation for a guide.

After obtaining the needed connection properties, accessing QuickBooks data in PowerShell consists of three basic steps:

  1. Install the module.

    Install-Module QuickBooksCmdlets

  2. Connect to QuickBooks:

    $quickbooks = Connect-QB

    If this is the first time you are using the cmdlets, you will be asked to license the installation.
    Enter 'TRIAL' to activate a free, 30-day trial.

  3. Retrieve the data from the Credits, Invoices, and Payments tables based on the start and end dates for the period and the customer name.

    $invoices = Select-QB -Connection $quickbooks -Table Invoices -Where "CustomerName = '$CustomerName' AND IsPaid = 'False' AND Date >= '$QBStartDate' AND Date <= '$QBEndDate'"
$credits = Select-QB -Connection $quickbooks -Table CreditMemos -Where "CustomerName = '$CustomerName' AND CreditRemaining > 0"
$payments = Select-QB -Connection $quickbooks -Table ReceivePayments -Where "CustomerName = '$CustomerName' AND Date >= '$QBStartDate' AND Date <= '$QBEndDate'"

Reconciling Authorize.Net & QuickBooks Transactions

With the data collected from the Authorize.Net Batches table and Quickbooks Invoices, CreditMemos, and ReceivePayments tables, you are now ready to reconcile transactions. Iterate through the batches returned from Authorize.Net and pull all transactions from Authorize.Net associated with each batch. With the transactions collected, iterate through any relevant accounts and check the status of each transaction. If the transaction was settled successfully, add the amount to the running total of matched invoices. If the transaction was settled as a refund, add the amount to the running total of matched credits.

As you iterate through the transactions, collect the necessary payment or credit information to be pushed to the ReceivePayments table in QuickBooks (for matched invoices) and Checks table in QuickBooks (for matched credits). The script presented below, while not necessarily complete, demonstrates the process described above and showcases the utility of the CData Cmdlets for Authorize.Net and QuickBooks. 

#iterate through the Authorize.Netbatches
foreach ($batch in $batches) {
  
  $batchid = $batch.BatchId
  
  #find payments for this batch
  $payment = $payments | Where-Object ReferenceNumber -like "*-$batchid"
  if ($payment) {
    continue
  }
  
  #find Authorize.Nettransactions for this batch
  $transactions = Select-AuthNet -Connection $authorizenet -Table "TransactionList" -Where "BatchId = '$batchid'"

  #identify accounts
  $accounts = @{
    'one' = "Bank Accounts:Account1";
    'two' = "Bank Accounts:Account2";
  }

  #iterate through the accounts
  foreach ($e in $accounts.GetEnumerator()) {
  
    $batchref = "$($e.Name)-$batchid";
    $batchaccount = $e.Value;

    $invoice_appliedto = '';
    $invoice_nomatch = '';

    $credit_appliedto = '';
    $credit_nomatch = '';
    
    $batch_invoice_total = 0
    $batch_credit_total = 0
    
    $matched_invoice_total = 0
    $matched_credit_total = 0

    #iterate through the transactions
    foreach ($transaction in $transactions) {

      #match transactions to an account
      if ($e.Name -eq 'one') {
        if ($transaction.AccountType -ne 'Account1') {continue}
      } else {
        if ($transaction.AccountType -eq 'Account2') {continue}
      }
      
      #find the settlement amount
      $amount = $transaction.SettleAmount

      #was the transaction settled? refunded?
      switch ($transaction.TransactionStatus) {
      
        "settledSuccessfully" {
        
          $batch_invoice_total += $amount;
          
          $invoice = $invoices | Where-Object POnumber -eq $transaction.InvoiceNumber
          if (($invoice | Measure-Object).Count -gt 1) {
            $invoice = $invoice[0]
          }
          
          if (!$invoice) {
            $invoice_nomatch += " $($transaction.InvoiceNumber)"
            continue
          }

          if ($amount -gt $invoice.Amount) {
            $amount = $invoice.Amount
          }
          
          $matched_invoice_total += $amount
          
          #prep the invoice row for QuickBooks
          $invoice_appliedto += "<Row>"
          $invoice_appliedto += "<AppliedToPaymentAmount>$amount</AppliedToPaymentAmount>"
          $invoice_appliedto += "<AppliedToRefId>$($invoice.ID)</AppliedToRefId>"
          $invoice_appliedto += "<AppliedToTxnType>Invoice</AppliedToTxnType>"
          $invoice_appliedto += "</Row>`r`n"

          # now remove the matched invoice from the list
          $invoices = $invoices | Where-Object ID -ne "$($invoice.ID)"
        }
        
        "refundSettledSuccessfully" {

          $batch_credit_total += $amount;

          $credit = $credits | Where-Object POnumber -eq $transaction.InvoiceNumber
          if (($credit | Measure-Object).Count -gt 1) {
            $credit = $credit[0]
          }
          
          if (!$credit) {
            $credit_nomatch += " $($transaction.InvoiceNumber)"
            continue
          }

          if ($amount -gt $credit.Amount) {
            $amount = $credit.Amount
          }
          
          $matched_credit_total += $amount

          # now remove the matched credit memo from the list
          $credits = $credits | Where-Object ID -ne "$($credit.ID)"

          $pmt = @{
            "Payee"                 = "CRM Customer"
            "Account"               = $batchaccount
            "Date"                  = $batch.SettlementTime
            "ExpenseItemAggregate"  = "<CheckExpenseItems><Row><ExpenseAccount>Accounts Receivable</ExpenseAccount><ExpenseAmount>$($credit.Amount)</ExpenseAmount></Row></CheckExpenseItems>"
            "ApplyCheckToTxnId"     = $credit.ID
            "ApplyCheckToTxnAmount" = $amount
            "Memo"                  = "BATCH $batchref [REFUND]"
          }
          
          #add a new check to QuickBooks
          if (!$TestMode) {
            Add-QB -Connection $qb -Table "Checks" -Columns ([array]$pmt.Keys) -Values ([array]$pmt.Values)
          }
        }
        
        default {
          Write-Output "UNSUCCESSFUL TRANSACTION (IGNORED): $($transaction.InvoiceNumber) `$$($transaction.SettleAmount)"
        }
      }
    }

    #invoice amounts do not match
    $batchmemo = "BATCH $batchref"
    if ($batch_invoice_total -ne $matched_invoice_total) {
      $batchmemo = "BATCH $batchref ***UNBALANCED*** [`$$($batch_invoice_total - $matched_invoice_total)] $invoice_nomatch"
    }
    
    #credit amounts do not match
    if ($batch_credit_total -ne $matched_credit_total) {
      $batchmemo = "BATCH $batchref ***UNBALANCED*** [`$$($batch_credit_total - $matched_credit_total)] $credit_nomatch"
    }
    
    #build and add a payment to QuickBooks
    if ($batch_invoice_total -ne 0) {
      if ($invoice_appliedto -ne '') {
        $invoice_appliedto = "<ReceivePaymentsAppliedTo>$invoice_appliedto</ReceivePaymentsAppliedTo>"
      }
      $pmt = @{
        "ReferenceNumber"           = $batchref
        "CustomerName"              = "CRM Customer"
        "Date"                      = $batch.SettlementTime
        "Amount"                    = $batch_invoice_total
        "DepositToAccountName"      = $batchaccount
        "AppliedToAggregate"        = $invoice_appliedto
        "AutoApply"                 = if ($invoice_appliedto -ne '') {"Custom"} else {"FutureTransactions"}
        "Memo"                      = $batchmemo
      }
      
      if (!$TestMode) {
        $count = Add-QB -Connection $qb -Table "ReceivePayments" -Columns ([array]$pmt.Keys) -Values ([array]$pmt.Values)
      }
    }
  }
}

More Information & Free Trials

For more information on the CData PowerShell DataCmdlets and to download a free, 30-day trial of the cmdlets, visit the DataCmdlets page. As always, our world-class support team is available to help. If you have any questions about the DataCmdlets, please let us know.