Building an Auto Reconciliation Tool for Xero

Xero is a pretty cool tool for accounting. It helps you to input all your income and outgoings and populates charts which tell you what is going on. Profit & Loss, Balance Sheet and all the good stuff! I wish I could say that I had this stuff organized from the beginning but that would be a big lie. Roomfilla was incorporated in August 2015. I have just finished putting the best part of 4 years of transactions into Xero and found some issues.

No Auto Reconcile / Bulk Reconcile in Xero

With the bank feeds that you can integrate with Xero, chances are your bank will feed transactions directly into the system. The problem is if you find yourself in the situation that I found myself in and having to manually input a bunch of old transactions into the system. I sped this process up massively using Zapier and Parseur and a few other tricks but even after that, you get an orange unreconciled mark on the transaction… this kinda sucks. The only way you can really get this done is to go into the transaction, click a menu button and then click a button to reconcile. Long. Winded. Nonsense.

It was at this point that I thought there must be a better way to do it. I then remembered a tool I have used for such monotonous tasks before called Fake and got to work with building a workflow and it was surprisingly straightforward to do.

The Outline

The workflow I wanted to create needed to automatically click on the transactions which were not reconciled and do the clicks in the right places to reconcile those documents. Simply really. I thought it would be good if I could set the amount of loops that the flow would run through and also put in some conditions. I saw that on the main statement page ‘unreconciled’ appeared so I could definitely use that as a condition to proceed. Also when clicking ‘Mark as reconciled’ it loads a success message. Again I can use this as a condition to only proceed if this message appeared.

The Process

It really is super simple to build this in Fake. First of all you need to create a repeat block and you can toggle that to repeat as many times as you need to. Secondly, you want to load the URL of the bank statement with the ‘unreconciled’ transactions on it. You will need to filter this by the reconciled row to ensure you get the unreconciled transactions at the top. I then created a mouse event to click the top one in the list. You can hold ctrl and drag to where you want to click. Super easy! After this I created a mouse event to click the menu, and again to click the ‘Mark as Reconciled’ tab. You can see at the start I have a condition to ensure there are some transactions to be reconciled and at the end I have a condition waiting for the success message.

This is a success! A simple workflow to save you time clicking through all this nonsense yourself. Set it for how many transactions you have to reconcile and get on with something else.

That’s about it. There are a lot of boring admin tasks that can be automated with tools like Fake. I had 4 years worth of transactions to do here and it made the process a lot more pleasant. A bulk reconcile tool for Xero would be super useful or a way to do it in their system but it simply doesn’t exist. This is a nice workaround to do it quickly. Quick side note – I can understand why they don’t have a bulk tool as these transactions should all be checked and confirmed. We did this before they reached Xero though so it was a case of trying to get them in the system as efficiently as possible. I hope this has helped someone!

Airbnb Amendment / Alteration Scam

This post is aimed at Airbnb hosts. There are unfortunately ways that guests can use the mechanisms in Airbnb to get out of their reservations penalty free and override all cancelation policies. Unfortunately Airbnb support have not shown any interest in helping out hosts who fall victim to this despite guests clearly showing that they have done this trick for this specific purpose. With that cleared up, let’s get into how this is possible.

What enables the scam?

Put simply, Airbnb introduced a grace period in 2018. The grace period enables guests to cancel a reservation for 48 hours penalty free if the booking starts later than 14 days in the future. The grace period has issues and was not well received by hosts especially those on the strict cancelation policy. It was another policy created at host’s detriment to compete directly with the larger online travel agencies (OTAs).

The issue with the grace period for accommodation is, you can book somewhere, receive all the contact details, address etc from the host and then cancel. Most properties on Airbnb are residential and not hotels like other OTAs. This causes a security threat and a way for unscrupulous characters to gather leads.

Why amendments?

I get it, plans change and sometimes people need to make changes to their trip. This is fine. Airbnb allows both hosts and guests to send amendments for their trip. Both parties have to agree to an amendment to make the date changes confirmed. There are stories about people booking a minimum stay 3 night property and sending amendments for 2 nights, people using amendments and then their card payment failing and the main point of this blog post, people using the amendment feature to reset the grace period and cancel penalty free.

The Airbnb amendment system is broken. If a guest sends an amendment and the host doesn’t want to accept it then you just increase the chances of retaliatory reviews. Airbnb puts such an emphasis on reviews and screws over hosts who might have a legitimate reason for not wanting to accept an amendment. This shouldn’t happen and there should be more protection. Unfortunately Airbnb has grown too fast and you only have to look at their trustpilot page to see what people think of their customer support.

How to prevent this?

It is difficult. People send amendments for a variety of reasons. Some genuine and some amendments are sent with the pure purpose of resetting the grace period so a penalty free cancelation can be made. Airbnb should really step up and build into their systems that once a booking is confirmed there is a 48 hour period. If an amendment is sent, this is not reset. It is lazy development and hosts should never be put in a position of being scammed out of a reservation they have already confirmed.

Some hosts I have spoken to have a blanket no amendment policy and others I have spoken to will verbally confirm the amendment but they will not click the buttons to confirm it on the system. How you handle amendments as a host is up to you. Just be warned, that if someone is asking to make an amendment and the dates are more than 14 days in the future, they may well be doing so just to make a cancel and override your cancelation policy.

Cheap Alternative to Webmerge / Docupilot using Zapier!

Before I start, I’m not saying not to use these tools but there is a cheaper alternative to them. It is somewhat limited so if you are looking for a fuller solution then eat your heart out. Coming from a startup founder’s perspective where we’re watching every penny we spend, there is a way to create the effects of Webmerge and Docupilot without having to spend the money on another bit of SaaS.

For context, Webmerge has a starting plan of $29 but only includes 30 merges so realistically most businesses would be on the next plan which is $99. Docupilot does 100 documents for $29 and 500 for $99. Do you as a small business have an extra $1,000+ to spend on something like this? That is a decision you need to make!

Why Create Documents from Templates?

There are various reasons why your business might be able to save time by creating documents from templates. If you provide tickets, receipts or personalized guides then creating specific tailored documents to your users shows them that you are providing them with that extra service! It also can help your business document receipts / invoices where no two documents are the same. Doesn’t sound bad and it isn’t!

For context, how do I use a document merge service? At Roomfilla, we provide all guests with an e-ticket after booking. The e-ticket has all their booking details on with the check in and check out date. Providing this to guests makes check in easy as they can provide it at the reception of properties and hey presto, smooth, simple and effective checkins take place. We handle quite a volume of bookings on a monthly basis so were looking for a way to do this with the systems that we already use in a cheap and effective manner. I am sure if we used a service like Docupilot or Webmerge then our e-tickets would look a lot prettier, but for the sake of hacking a solution together which doesn’t really cost anything. We decided to use Google Docs. Yes, that is correct – Google Docs.

The Workflow is Simple

Firstly, you probably want to go to Google Docs and create a new document. Use this as your template so create the document as you need it to look. Where you want to add the custom data, just put fields like ‘{{PASSENGERNAME}}’, ‘{{BOOKINGID}}’ etc. It is important to put the double brackets round them. These fields will automatically be detected in Zapier later. Call the file something like ‘Ticket Template’ and remember where you put it in your Google Drive.

This is pretty easy to follow right?

The next step is to actually create the flow in Zapier. If you aren’t familiar with Zapier, it is pretty much a badass product which connects all the tools you use. They have a ton of integrations and you can create some quite advanced stuff relatively simply on it. For this use case, you need to have a trigger – which is where you will get the information you need from and then the action which will be to create the Google Doc document.

A trigger can be anything from a Stripe payment, Typeform submission, Webhook received or in the case I use it an email is parsed with Parseur. We parse all of our confirmed booking emails and then use the data fields from the parse to create our document. After you have set up your trigger source you can go to the action. Here you want to set up your Google Doc permissions and hit the create document from template option.

On the next page, you will need to find the document and fill in the custom fields. When you pull the document in, it will auto generate those fields. Pretty cool right!

What I have done here just for the demo purpose is to take the fields from a push by zapier trigger. You can see how I auto generate those fields, so they will change every time we receive a booking. I can even change the name of the document each time. Here I am using <guest name> – <confirmation code>.

Now that this has been done. It is time to test it and see if it works! Once you have the success message in Zapier, go back to your Google Drive and find the file. Open it up and have a look to see if you have success or not.

Woohoo, it works!

That’s pretty cool right! So that is the basics of creating a document merging data from other software you may use into it. Now that this has been done, there is a ton of other things you can automatically generate. For example, in the workflow I use for Roomfilla, we keep all guest details in a CRM system. We can automatically take this Google Doc document, convert it to PDF. Upload the PDF to a specific dropbox folder, get the share link for that document and add it to the CRM profile of the guest.

This works super well as we have an automated message triggered from our systems 3 days before check in providing them with the e-ticket. This is a separate Zapier flow but grabs this document and sends it to guests and helps us with smooth checkins.

I hope this post has been useful for those looking for a cheap alternative to their document merging needs. There are of course some costs involved in this. You will need to be able to use Zapier, have a source of data which works with Zapier and proficiency in using Google Docs. There are so many things which can be automated and I hope this can spark the interest in some people to try a few things outside of their comfort zone!