This year started with my wife deciding to work as an independent IT consultant. This new challenge also brought new opportunities for us. One such challenge was to find an effective way to keep track of all her business expenses to be expensed at the end of the year. Like everyone else we looked at various solutions but couldn't find something that was a good fit for our requirements and bills.
- Mobile apps like wave read all your receipts and the total amount, not the amount before tax which is what you need to keep track of.
- The category of expenses that comes with the app may not be suitable for your business. There is no way to customize it.
- The bills are saved on their database, so you don't have access to them directly.
The solution :
We decided to use google serverless to create a software system to achieve our goal. Serverless will keep the backend logic. The front end will be hosted on a free website (weebly). The front end will capture the expenses and pass it to the backend. Serverless can interact with google backend open API to save details to google drive and google spreadsheets. Spreadsheets can be used to prepare dynamic reports on tableau public which can be linked back to the webpage, so as to see a report of expenses.
How we did it:
We created a serverless backend script on Google app script . This is hosted on my google drive. Next, we created a simple front end to interact with the backend app script.
The front end had all the categories and sub-categories of expenses that we wanted to record. It gave a field to manually write the amount and to upload the image of the bill.
Once the front end submits the input, the backend catches the field and interacts with the google API to save the details in the google drive. For that, we created a google spreadsheet that has all the defined API endpoints to interact with. All the data (category, sub-category, amount, date, link to file image) was saved in the excel for tax reporting purposes. The actual file was uploaded to a defined folder on google drive with a similar name convention so as to make it easily traceable for humans. The Google Drive API gives the link to the image when you upload, so you can save it to the same excel file, so as to make a foreign-key link.
My drive looks like this:
and the spreadsheet for this looks like this:
As a final piece to this activity, we linked the spreadsheet to a tableau report online which keep tracking the data and creating graphs.
Finally, we posted all this on a webpage on our blog, so as to access it easily.
So whenever we want to capture the expense, we open the blog page and post a picture of the receipt with all the inputs and it reflects on the tableau report on the same page a while later.
The page looks something like this:
We will be sourcing out the code after a while after we make it flexible enough for consumption by non-tech folks. Please keep following my blog to get an update.