Importing transactions into Mint.com using C# and Selenium

• Download UpdateMint.zip - 10.2 KB

Introduction

This article presents a C# command line utility that uses Selenium to automate importing transactions into mint.com.

While playing around with the iPhone app, I accidentally deleted my credit card account, thus losing ~10 years worth of transactions. Searching the internet uncovered one article, http://aaronfrancis.com/2013/importing-transactions-into-mint/, and a lot of feature requests to Intuit. I wanted to create a solution that was as robust and foolproof as possible.

My first approach was to use Javascript, in the browser’s developer tools console, to read the csv file and manipulate the DOM. However, this approach ran into several issues:

1. Couldn't read the csv file from desktop per browser restrictions. Worked around that by putting the data into a literal string.
2. Couldn’t inject jquery and jquery-csv into the page in order to process csv string above. Whenever the form was submitted, the whole page got wonky. Worked around that by manually converting the csv into a json literal string.
3. Couldn’t wait out of process for the page to refresh after submitting a transaction. Using a two transaction test set, only the last transaction was submitted. No workaround was possible.

My second approach was to attempt a direct form post. I was unable to reverse engineer the page code to that level. I looked at the HTTP packets, but came to suspect that even if I could manually create them, authentication would still be an obstacle.

Finally I settled on C# plus Selenium. C# would give me an easy command line utility, rich library support for dealing with the csv file, and be out of process. Selenium is for “automating web applications for testing purposes”. While this project is not testing, it is precisely web application automation. Selenuim also integrates very easily in a C# solution via NuGet packages.

Background

The basic workflow is simple: get a csv file of transactions from the bank or credit card website and then automate the mint.com “add transaction” dialog.

CSV File

Downloading credit card transactions is straightforward. Refactoring the CSV to [Date, Merchant, Category, Amount], cleaning up the data, and applying categories, while tedious, is also straightforward. 

However, keep the following in mind:

• Some categories will not work, like Transfer and Credit Card Payment. Do not import “Transfer from XXX” items since they will have to be marked as an expense and this will mess up the various Trend graphs.
• Beware of non-standard single quotes.
• Ensure that the “negative” transactions start with a minus sign and are not in parenthesizes.
• Do not have any blank lines at the end of the file.

A sample input file:

Date,Merchant,Category,Amount
01/01/2016,TestA,Financial,$1.00
01/01/2016,TestB,Financial,-$1.00
01/02/2016,TestC,Financial,-$1.00
01/02/2016,TestD,Financial,$1.00
01/03/2016,TestE,Financial,$1.00

Mint.com Web Elements

Using the Chrome Developer Tools with the DOMListener plugin, it is possible to find all of the relevant elements and see what changes when adding a transaction.

Login page

Note: After logging in, there is a progress page, and then the main page is displayed.

Main navigation bar

Note: After clicking Transactions, there is a very brief progress page, then the new page is rendered, but it is a bit “dithered”, until it rerenders fully.

Transaction button

Note: After clicking the “+ Transaction” button, the transaction form is displayed, overlaying the first entry in the transaction list.

Transaction form

Note: After submitting a transaction, the page appears a bit “dithered” again, and then re-renders.

Note: The form and its fields are always present in the DOM. What seems to change is the form’s class, from “expense hide” to “expense” or “income” and then back.

Note: Transactions are added as “cash transactions”. They are not tied to any specific account. But they are deletable.

Using the code

The solution creates a simple console app that accepts the following key-value pair parameters. The parameters can start with a / or -- and can use either = or : to separate the key from the value, e.g. “--transactionfile=c:\temp\testdata.csv”.

// VS 2015 Community Edition
// NuGet: "Selenium.WebDriver", "Selenium.Support", and "Selenium.WebDriver.ChromeDriver"

using System;
using System.Linq;
using OpenQA.Selenium;
using OpenQA.Selenium.Chrome;
using OpenQA.Selenium.Support.UI;

namespace Project
{
   public static class Extensions
   { 
      // REFERENCE <a href="http://stackoverflow.com/questions/6229769/execute-javascript-using-selenium-webdriver-in-c-sharp">http://stackoverflow.com/questions/6229769/execute-javascript-using-selenium-webdriver-in-c-sharp</a>
      public static IJavaScriptExecutor Scripts(this IWebDriver driver)
      {
         return (IJavaScriptExecutor)driver;
      }
   }

   public class App
   {
      //// PROPERTIES
      public static string Date = System.DateTime.Now.ToString("yyyyMMddHHmm");
      public static string Self = System.Reflection.Assembly.GetEntryAssembly().Location;
      public static string Root = <a href="http://system.io/">System.IO</a>.Path.GetDirectoryName(Self);
      public static ArgumentTable Arguments = new ArgumentTable();
      public static LogFile Log = new LogFile();
      public static int ExitCode = 0;

      //// METHODS
      public static int Main(string[] args)
      {
         try
         {
            // start
            if (!Arguments.ContainsKey("transactionfile")) Arguments["transactionfile"] = <a href="http://system.io/">System.IO</a>.Path.Combine(Root, "transactions.csv"); // default to .\transactions.csv
            if (!Arguments.ContainsKey("name")) { Console.Write("Enter email or user id: "); Arguments["name"] = Console.ReadLine(); }      // required
            if (!Arguments.ContainsKey("password")) { Console.Write("Enter password: "); Arguments["password"] = ReadPassword(); }          // required
            if (!Arguments.ContainsKey("logfile")) Arguments["logfile"] = <a href="http://system.io/">System.IO</a>.Path.Combine(Root, String.Format("{0}-{1}.log",<a href="http://system.io/">System.IO</a>.Path.GetFileNameWithoutExtension(Self), Date));
            Log.Open(Arguments["logfile"]);
            Log.Message("Start");
            Log.Debug(Arguments.ToString());

            // 1. load csv data into an array of objects
            Log.Trace("Loading CSV data...");
            System.Collections.Generic.List<Transaction> Transactions = <a href="http://system.io/">System.IO</a>.File.ReadAllLines(Arguments["transactionfile"]).Skip(1).Select(v => Transaction.FromCsv(v)).ToList();

            using (IWebDriver driver = new OpenQA.Selenium.Chrome.ChromeDriver())
            {
               var wait = new OpenQA.Selenium.Support.UI.WebDriverWait(driver, TimeSpan.FromSeconds(10));

               // 2. open <a href="http://mint.com/">mint.com</a>
               Log.Trace("Opening website...");
               driver.Url ="<a href="https://mint.intuit.com/login.event?referrer=direct&soc=&utm=">https://mint.intuit.com/login.event?referrer=direct&soc=&utm=</a>";

               // 3. login
               Log.Trace("Logging in...");
               wait.Until(ExpectedConditions.ElementIsVisible(By.Id("ius-userid")));
               wait.Until(ExpectedConditions.ElementIsVisible(By.Id("ius-password")));
               wait.Until(ExpectedConditions.ElementIsVisible(By.Id("ius-sign-in-submit-btn")));
               driver.FindElement(By.Id("ius-userid")).SendKeys(Arguments["name"]);
               driver.FindElement(By.Id("ius-password")).SendKeys(Arguments["password"]);
               driver.FindElement(By.Id("ius-sign-in-submit-btn")).Submit();

               // 4. navigate to transactions page
               Log.Trace("Navigating to transaction page...");
               wait.Until(ExpectedConditions.ElementToBeClickable(By.CssSelector("a[href*='transaction.event']")));
               driver.FindElement(By.CssSelector("a[href*='transaction.event']")).Click();
               System.Threading.Thread.Sleep(3000); // MAGIC, let the new page load; sometimes the first transaction fails because the form is add-cash but the fields are an existing transaction and not "Enter Description"

               // 5. import transactions
               Log.Trace("Importing transactions...");
               foreach (var Transaction in Transactions)
               {
                  Log.Debug("Found {0}", Transaction.ToString());

                  // a. open form
                  Log.Trace("Opening form..");
                  wait.Until(ExpectedConditions.ElementExists(By.Id("txnEdit")));
                  wait.Until(ExpectedConditions.ElementExists(By.Id("txnEdit-form")));
                  wait.Until(ExpectedConditions.ElementToBeClickable(By.Id("controls-add")));
                  Log.Debug("#txnEdit class = {0}", driver.FindElement(By.Id("txnEdit")).GetAttribute("class"));
                  wait.Until(d => d.FindElement(By.Id("txnEdit")).GetAttribute("class") == "single regular");
                  Log.Debug("#txnEdit-form class = {0}", driver.FindElement(By.Id("txnEdit-form")).GetAttribute("class"));
                  wait.Until(d => d.FindElement(By.Id("txnEdit-form")).GetAttribute("class").Contains("hide") == true);
                  driver.Scripts().ExecuteScript("document.getElementById('controls-add').click()"); // driver...Click() sometimes failed

                  // b. enter values
                  Log.Trace("Entering values..");
                  Log.Debug("#txnEdit class = {0}", driver.FindElement(By.Id("txnEdit")).GetAttribute("class"));
                  wait.Until(d => d.FindElement(By.Id("txnEdit")).GetAttribute("class") == "add cash");
                  Log.Debug("#txnEdit-form class = {0}", driver.FindElement(By.Id("txnEdit-form")).GetAttribute("class"));
                  wait.Until(d => d.FindElement(By.Id("txnEdit-form")).GetAttribute("class").Contains("hide") == false);
                  wait.Until(ExpectedConditions.ElementToBeClickable(By.Id("txnEdit-date-input")));
                  wait.Until(ExpectedConditions.ElementToBeClickable(By.Id("txnEdit-merchant_input")));
                  wait.Until(ExpectedConditions.ElementToBeClickable(By.Id("txnEdit-category_input")));
                  wait.Until(ExpectedConditions.ElementToBeClickable(By.Id("txnEdit-amount_input")));
                  Log.Debug("#txnEdit-merchant_input value = {0}", (string)driver.Scripts().ExecuteScript("return document.getElementById('txnEdit-merchant_input').value"));
                  wait.Until(d => (string)d.Scripts().ExecuteScript("return document.getElementById('txnEdit-merchant_input').value") == "Enter Description");  // the most important safety check, otherwise you might override existing data
                  driver.Scripts().ExecuteScript("document.getElementById('txnEdit-date-input').value = arguments[0]", Transaction.Date); // .SendKeys doesn't work for this field
                  driver.FindElement(By.Id("txnEdit-merchant_input")).SendKeys(Transaction.Merchant);
                  driver.FindElement(By.Id("txnEdit-category_input")).SendKeys(Transaction.Category);
                  driver.FindElement(By.Id("txnEdit-amount_input")).SendKeys(Transaction.Amount);
                  if (Transaction.Type == TransactionType.Expense)
                  {
                     driver.FindElement(By.Id("txnEdit-mt-expense")).Click();
                     if (driver.FindElement(By.Id("txnEdit-mt-cash-split")).Selected) driver.FindElement(By.Id("txnEdit-mt-cash-split")).Click();
                  }
                  else
                  {
                     driver.FindElement(By.Id("txnEdit-mt-income")).Click();
                  }
                  driver.FindElement(By.Id("txnEdit-note")).SendKeys("Imported transaction.");

                  // c. submit form
                  Log.Trace("Submitting form..");
                  if (!Arguments.ContainsKey("whatif")) // submit
                  {
                     driver.FindElement(By.Id("txnEdit-submit")).Click();
                  }
                  else // pretend
                  {
                     driver.FindElement(By.Id("txnEdit-cancel")).Click();
                  }
                  Log.Message("Imported {0}", Transaction);
                  System.Threading.Thread.Sleep(3000); // MAGIC, safety net, let the submit cook
               }
            }
         }
         catch (Exception ex)
         {
            Log.Exception(ex.Message);
            ExitCode = 255;
         }
         finally
         {
            // finish
            Log.Message("Finished [{0}]", ExitCode);
            Log.Close();
         }

         return ExitCode;

      }

      public static string ReadPassword()
      {
         string Password = "";
         ConsoleKeyInfo KeyInfo = Console.ReadKey(true);
         while (KeyInfo.Key != ConsoleKey.Enter)
         {
            if (KeyInfo.Key != ConsoleKey.Backspace)
            {
               Password += KeyInfo.KeyChar;
               Console.Write("*");
            }
            else if (Password.Length > 0)
            {
               Password = Password.Substring(0, (Password.Length - 1));
               Console.Write("\b \b");
            }
            KeyInfo = Console.ReadKey(true);
         }
         Console.WriteLine();
         return Password;
      }
   } // class
} // namespace

Additional Details

• iMac, 27in, Late 2013
• [Bootcamp] Windows 10 (fully patched)
• Visual Studio 2015 Community Edition
• NuGet packages: Selenium.Support, Selenium.WebDriver, Selenium.WebDriver.ChromeDriver
• Chrome

Points of Interest

The hardest part was figuring out when it was safe to manipulate the DOM. As noted above, many of the fields are always present, it is a matter of their “state”. Furthermore, based upon the behavior I observed, I suspect the page code is Ajax-based, making it even harder to know when something is ready or done. I settled on a pattern of using Wait.Until() statements as preconditions for the fields that were about to be manipulated and Thread.Sleep() to wait for page load and form submission. Obviously, using Thread.Sleep() is not good practice. But the pragmatic, and in this case safe approach, was to use them. The timeout values for both were calculated via trial and error with some round up. The values would be a function of mint.com service performance, network latency, and local CPU performance. They may need to be adjusted for your circumstances.

I ended up importing a month’s worth of transactions, about 100, at a time. Over 24 imports, there were about 4 failures, excluding “bad data”, due to timing. When a failure occurred, between looking at mint.com and the log file, it was easy to pinpoint the last successful transaction, trim it and it predecessors from the csv file, and reattempt the import.

The LogFile and ArgumentTable utility classes are code that I wrote many years ago and keep reusing over and over.

History

2017-04-23 Original draft.

2017-04-24 Removed blank lines in sample code.