Using Wave and Twilio to simplify accounting

By Jonathan Poland

With my second year of rentals complete, I've started to look at improvements.  There are a few pain points I set out to solve:

  • Every month I would process scanned receipts and pair them up with bank statements to consolidate them into a google sheet.  I often misplaced receipts and hated scanning and saving them to Google Drive.  Plus, the manual copying of data from my bank into the spreadsheet sucked.
  • I often forgot to log the mileage for when I visited the property and why.

Wave

I decided to drop the spreadsheet and use Wave instead.  Wave is a free online accounting software.  It's meant for running a full business of buying and selling inventory, but it works well for my limited use cases.

It can directly import transactions from your bank, or import CSV statements.  It provides nice dashboards to see cash flow and other metrics.  Every transaction has a checkmark you can use to note that you've reviewed it.

You can also email receipts or take pictures of receipts with the mobile app and have them saved to later be paired up with transactions.  It uses pretty good image recognition and is able to process receipts automatically.

You can also accept online payments via credit cards, debit cards, or ACH transfer.  I've already used it to take one payment for an upcoming rental.  It looks a lot more professional to customers than PayPal.  Coupled with digital signatures covered previously, this covers me to operate outside VRBO.

I'm really quite happy with it.  It's miles better than the spreadsheet.

Twilio

Now with everything coming directly from my bank into wave, I needed a better way to handle my mileage.  Wave doesn't have anything to help with this.  For my use case, I pretty much always drive the same miles: from home to the rental and back.  I just wanted a way to say: "I drove to the rental today to do X" and "I drove home today".

I experimented with a few things: Smart Things presence, IFTTT locations alerts, Google location data, and the automator app for android.  The most common problem is geolocation on android is unreliable.  Google location data is great, but only available as a download of all history in a somewhat unfriendly format.  And, at the end of the day, none of those integrates with Wave.

Since I was already using Twilio for the rental, I decided to leverage that.  I modified my sms function to handle my two cases:

  1. When I text "arrive <reason>" it posts a new transaction to wave using today's date, my fixed number of miles times the IRS mileage, and my <reason>.
  2. When I text "depart", it posts a similar transaction noting I returned home.

Here's the code for that Twilio function:

function wavepost(command, callback) {
 let got = require('got');
 let dateformat = require('dateformat');
 
 const MILERATE = 0.58;
 const MILES = 67;

 let now = new Date();
 var description;
 if (command.startsWith('arrive')) {
 description = "GR -> SH: " + command.substr(7);
 } else {
 description = "SH -> GR";
 }
 let amount = "" + (MILERATE * MILES);
 let postData = {
 query:"mutation($input: MoneyTransactionCreateInput!) {moneyTransactionCreate(input: $input) {didSucceed inputErrors {code message path}}}",
 variables:{
 input:{
 businessId: process.env.BUSINESS_ID,
 description: description,
 externalId: "" + Date.now(),
 date: dateformat(now, 'isoDate'), 
 anchor:{
 accountId: process.env.ACCOUNT_ID,
 amount: amount,
 direction:"WITHDRAWAL"
 },
 lineItems:[]
 }}
 };
 got.post('https://gql.waveapps.com/graphql/public', {
 body: postData, 
 headers: {
 'Content-Type': 'application/json',
 Authorization: 'Bearer ' + process.env.API_KEY,
 }, 
 json: true
 })
 .then(function(response) {
 if (response.body.data.moneyTransactionCreate.didSucceed) {
 callback(null, "Created " + description + " $" + amount);
 } else {
 callback(null, "Failed: " + response.body) 
 }
 }, function(reason) {
 callback(null, reason);
 })
 .catch(function(error) {
 callback(null, error);
 });
}


exports.handler = function(context, event, callback) {
 const MY_NUMBER = '+1XXXXXXXXXX'; // Use your number
 var msg;
 
 let twiml = new Twilio.twiml.MessagingResponse();
 if (event.From === MY_NUMBER) {
 var cmd = event.Body.trim().toLowerCase();
 if (cmd.startsWith('arrive') || cmd.toLowerCase() == 'depart') {
 return wavepost(cmd.toLowerCase(), callback);
 }
 const separatorPosition = event.Body.indexOf(':');
 if (separatorPosition < 1) {
 msg = twiml.message('You need to specify a recipient number and a ":" before the message.');
 } else {
 const recipientNumber = event.Body.substr(0, separatorPosition).trim();
 const messageBody = event.Body.substr(separatorPosition + 1).trim();
 msg = twiml.message({to: recipientNumber});
 msg.body(messageBody);
 }
 } else { 
 msg = twiml.message({to: MY_NUMBER});
 msg.body(`${event.From}: ${event.Body}`);
 }
 for (i = 0; i < event.NumMedia; i++) { 
 msg.media(event[`MediaUrl${i}`]);
 }
 callback(null, twiml);
};

This does require setting a few environment variables for your functions:

  •  BUSINESS_ID: You'll need to explore the wave API a bit to find this for your business.
  • ACCOUNT_ID: You'll need to explore the API a bit to find this for your business.
  • API_KEY: You'll need to generate this using the Wave API website.

You can learn about the Wave API here.

So for now, I just need to remember to send those simple texts when I come and go.  In the future, if I find a reliable way to note my visits automatically, I could automate this with only a small change to the function above.