BofA and PNC daily balance e-mail consolidator with running tally

README.md

Overview

Bank of America and PNC send a daily balance e-mail of your accounts every AM. They send one email for each account.

This script will group them into one e-mail with a running balance of the last X days.

I have the script scheduled to run every AM after the emails usually arrive.

What It Does

The script generates a running tally of the last X days.

• The first column is the account name
• The next columns are the date in descending order
• The top line in each cell shows how much the change was
• The bottom shows the new total

Example:

How It Work

1. The script searches for all of the daily balance e-mails
2. Goes through them to extract balance data
3. Creates and sends the e-mail
4. Deletes daily balance e-mails older than the last X days

BofA and PNC Daily Balance Consolidator.js

var dailyBankingHistoryGmailSearchQuery = '(from:(onlinebanking@ealerts.bankofamerica.com) subject:("Your Available Balance") "your available balance") OR (from:(pncalerts@pnc.com) subject:(("Your Credit Card Balance" AND -"Your Credit Card Balance Is") OR "Your Savings Account Balance" OR "Your Checking Account Balance"))';
var dailyBalanceDaysToKeep = 5;

function getDailyBankBalance()
{
  // get all the emails
  var emailThreads = GmailApp.search(dailyBankingHistoryGmailSearchQuery);
  
  // if we have no mails then stop
  if(!emailThreads.length) return;
  
  // track all the balances and dates we have
  var accountBalanceData = {};
  var dateBalanceTracker = {};
  
  // go through each thread
  emailThreads.forEach(function(emailThread){
    // go through each message
    emailThread.getMessages().forEach(function(emailMessage){
      
      var accountName, accountBalance, balanceDate;
      
      // get the email sender
      var emailFrom = emailMessage.getFrom().match(/<(.+?@.+?)>$/)[1].toLowerCase();
      
      // different extraction logic depending on who the email is from
      switch(emailFrom)
      {
        case "onlinebanking@ealerts.bankofamerica.com":
          // use regex to extract balance data
          var balanceDetails = emailMessage.getPlainBody().match(/^(Balance|Account|Date): (.*)$/gm);
          accountBalance = Number(balanceDetails[0].split(": ")[1].replace(/[^0-9.]/g, ""));
          accountName = "BofA: " + balanceDetails[1].split(": ")[1];
          balanceDate = Utilities.formatDate(new Date(balanceDetails[2].split(": ")[1]), Session.getScriptTimeZone(), "yyyy-MM-dd");
          break;
        case "pncalerts@pnc.com":
          // get the html body
          var emailBody = emailMessage.getBody();
          
          // extract the account name
          var accountName = emailBody.match(/<h3.*?>\s*([^<]+?)\s*<\/h3>/);
          
          // if we found one
          if(accountName)
          {
            // get the account name
            accountName = "PNC: " + accountName[1];
            
            // get the account balance
            var accountBalance = emailBody.match(/<h4.*?>[^$]+Ledger Balance[^$]+[^$]+\$([\d\.,]+)[^<]+<\/h4>/i);
            if(!accountBalance) accountBalance = emailBody.match(/<h4.*?>[^$]+\$([\d\.,]+)[^<]+<\/h4>/);
            accountBalance = Number(accountBalance[1].replace(/,/g, ""));
            
            // get the balance date
            try
            {
              var balanceDate = emailBody.match(/<h2.*?>[^<]+ on (\d\d\/\d\d\/\d\d)[^<]+<\/h2>/)[1];
            }
            catch(e)
            {
              GmailApp.sendEmail("...", emailMessage.getSubject(), emailBody);
              throw new Error("dingo");
            }
            balanceDate = new Date(balanceDate.replace(/\/(\d\d)$/, "/20$1"));
            balanceDate = Utilities.formatDate(balanceDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
          }
          break;
        default:
          throw new Error("who?");
      }
      
      if(accountName)
      {
        if(accountName == "..." || accountName == "...")
        {
          accountBalance = accountBalance * -1;
        }
        
        // if the date is not in the date balance tracker then add it
        if(!(balanceDate in dateBalanceTracker))
        {
          dateBalanceTracker[balanceDate] = {
            "totalBalance" : 0,
            "differenceFromDayBefore" : 0,
            "emails" : []
          };
        }
        
        // save this email to the tracker so we can delete it later
        dateBalanceTracker[balanceDate]["emails"].push(emailMessage);
        
        // if the account name is not in the account balance data add it
        if(!(accountName in accountBalanceData)) accountBalanceData[accountName] = {};
        
        // save this date's account's balance
        accountBalanceData[accountName][balanceDate] = accountBalance;
      }
    });
  });
  
  Logger.log(JSON.stringify(accountBalanceData));
  Logger.log(JSON.stringify(dateBalanceTracker));
  
  // get an array of all the accounts and dates
  var accountBalanceAccounts = Object.keys(accountBalanceData).sort();
  var dateBalanceTrackerDates = Object.keys(dateBalanceTracker).sort().reverse();
  
  // styles
  var headingStyle=' style="border: 1px solid black; padding: 5px;"';
  var accountCellStyle = ' style="border: 1px solid black; padding: 5px; text-align: left"';
  var moneyCellStyle = ' style="border: 1px solid black; padding: 5px; text-align: right;"';
  
  // styles
  var emailOut = '<table style="border: 1px solid black; border-collapse: collapse;"><tr><th' + headingStyle + '>account</th>';
  
  // add the dates in the tracker to the header row
  dateBalanceTrackerDates.forEach(function(d){
    emailOut += '<th' + headingStyle + '>' + d + '</th>';
  });
  
  emailOut += '</tr>';
  
  // go through each account
  accountBalanceAccounts.forEach(function(accountName){
    // start a new row in the email
    emailOut += '<tr>';
    
    // print account name
    emailOut += '<th' + accountCellStyle + '>' + accountName + '</th>';
    
    // go through all the dates
    dateBalanceTrackerDates.forEach(function(oneDate, i){
      // get this dates balance
      // we may not have one so set it to 0
      var currentDayBalance = accountBalanceData[accountName][oneDate] || 0;
      
      // save the diffrence from the previous day
      var differenceFromDayBefore = 0;
      
      // we can't calculate differnce for the last (first) date cause we don't have anything before it
      if(i < (dateBalanceTrackerDates.length - 1))
      {
        var previousDayBalance = accountBalanceData[accountName][dateBalanceTrackerDates[i + 1]] || 0;
        
        differenceFromDayBefore = currentDayBalance - previousDayBalance;
      }
      
      // start a new cell
      emailOut += '<td'+ moneyCellStyle +'>';
      
      // print detla
      if(differenceFromDayBefore < 0)
      {
        emailOut += '<b style="color: red">' + moneyToCurrency_(differenceFromDayBefore) + '</b>';
      }
      else if(differenceFromDayBefore > 0)
      {
        emailOut += '<b>+' + moneyToCurrency_(differenceFromDayBefore) + '</b>';
      }
      
      // new line
      emailOut += '<br />';
      
      emailOut += moneyToCurrency_(currentDayBalance);
      
      emailOut += '</td>';
      
      dateBalanceTracker[oneDate]["totalBalance"] += currentDayBalance
      dateBalanceTracker[oneDate]["differenceFromDayBefore"] += differenceFromDayBefore;
      
    });
    
    emailOut += '</tr>'
  });
  
  // print totals
  emailOut += '<tr style="background-color: #ddd;"><th' + accountCellStyle + '>totals</th>';
  dateBalanceTrackerDates.forEach(function(oneDate, i){
    var totalBalance = dateBalanceTracker[oneDate]["totalBalance"];
    var differenceFromDayBefore = dateBalanceTracker[oneDate]["differenceFromDayBefore"];
    
    emailOut += '<td ' + moneyCellStyle + '>';
    
    if(differenceFromDayBefore < 0)
    {
      emailOut += '<b style="color: red">' + moneyToCurrency_(differenceFromDayBefore) + '</b>';
    }
    else if(differenceFromDayBefore > 0)
    {
      emailOut += '<b>+' + moneyToCurrency_(differenceFromDayBefore) + '</b>';
    }
    
    // new line
    emailOut += '<br />';
    
    // print the total
    emailOut += moneyToCurrency_(totalBalance);
    
    emailOut += '</td>';
  });
  
  emailOut += '</tr>';
  
  
  emailOut += '</table>';
  
  // send the email
  MailApp.sendEmail({
    "to" : "...",
    "subject" : "Daily Balances",
    "htmlBody" : emailOut
  });
  
  // delete emails older than the # we're keeping
  dateBalanceTrackerDates.slice(dailyBalanceDaysToKeep).forEach(function(oneDate){
    // delete all of the old emails
    dateBalanceTracker[oneDate]["emails"].forEach(function(oneEmail){
      oneEmail.moveToTrash();
    });
  });

}

// comma seperates thousands
function moneyToCurrency_(num)
{
  num = num.toFixed(2).split(".");
  return num[0].replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "." + num[1];
}