Do you track your personal/family expenses? If yes, I bet you use a specific software or service for this.

I personally think, there is nothing better than a simple Excel workbook for that task. It allows you to stay as flexible as you want, as simple or as complex as you want.

You can use Power Query to pull data from csv dumps from your bank accounts or currency exchange rates from your favourite financial web site, use Excel formulas to convert currency, Pivot Tables and charts to analyse and visualize data etc.

In this post you will see how you can enter data to your Excel spreadsheet with expenses from your mobile phone.

First things first

From the beginning we have to decide where to store Excel workbook for family expenses. File should be available online, shared with family member(s).

If you don’t have MS Office licenses provided by your employer, I’d consider Office 365 Home plan – 5 TB for 5 family members, or Free personal plan 5 GB max. By the way, Home plan is cheaper in Russian rubles (page in Russian) than in AUD or USD – just compare exchange rate.

Another benefit of storing an Excel workbook in OneDrive (and having Microsoft Accounts) – you can use MS Flow to seamlessly access it.

Setting up MS Flow

Assume you have already created a Microsoft Account (if not – you need to create it first). Then go to https://flow.microsoft.com and sign in with your MS Account credentials.

You can use search to find existing templates for tracking expenses

However, I’m going to show how you can create your own simple flow for this task.

It will also help you to understand how you can take existing template and adapt it for your needs.

Prepare Excel workbook

In target Excel workbook we must have a table.

We will need a table name later in flow, however, I couldn’t find a way to get table name in Excel Online.

When you create a new table, by default, it gets name “Table1” (if it is the table in the file), but if not, you will have to open file in Excel desktop to find out table name.

I’ll use “Table1” for demo.

Building a Flow

Before we start with the technical side of Flow, let’s think of how it should work.

Ideally, we want to enter data from any device – PC, tablet, mobile.

Mobile phone is usually the most difficult way. Because of small screen.

There is an Excel app, but I wouldn’t say it is easy to use.

I expect to have dropdown list for categories and currency; “amount” field restricted to numbers only; and timestamp or date automatically populated.

 

Let’s see what we can get from MS Flow.

We will start from blank

Find trigger “Button”

Button trigger is not just a “press button” action. It can be extended with input values

Exactly what we need – options for text and number.

I’ve selected a text and clicked ellipsis for options

We can enter as many options as we want

Add necessary parameters and press “New step”

Next step – is an Action. In our case – add/insert a row into an Excel table.

Depending on location of your file – choose corresponding option.

For this demo – it is “Excel Online (OneDrive)”

Find “Add a row into a table”

Theoretically, file can be stored in another account, so Flow requires creation of connection , during which we have to Sign In

Flow will also ask for permissions.

Obviously, if you want Flow to be able to edit files in your OneDrive, you have to grant permissions for that.

No option to share just one file, unfortunately.

Next step – path to file and table name

 

When we select a file, Flow will find existing tables in it

After selecting table we will see list of table columns

When we click into any field – we get addition menu on the right, where we can chose Dynamic content, e.g. value from previous steps of the flow.

For some reason, field “Amount” is hidden. I had to click “See more” to find it. Wasn’t obvious, I should say.

 

As we plan to share file for expenses with family members, it is a good idea to use default fields Flow provides – User email, User name and Timestamp.

When dynamic content is selected for all fields – don’t forget to set name of your Flow, then press “Save”

Test option will become available

Let’s finally test the flow!

Can’t wait! Continue!

As expected, we see a window with list of parameters we configured for Trigger step. Please note that all fields are mandatory!

Fill all fields and “Run flow”

OK, flow run successfully started, but did I get a new record in Excel file?

Let’s see flow run activity.

Not much info here – just time of execution and duration. 3 seconds! Who knows what takes the most of that time.

 

More details if you click on the record

It becomes very useful when something goes wrong. Flow creator can immediately see which step failed and see error message.

In our case, seems like all good. Time to check Excel file – voila

MS Flow on mobile phone

Find it in the corresponding app store, e.g. in Google Play, install and login with the same account you used for Flow creation.

Go to the “Buttons” area

Fill all fields (all are mandatory), and tap on “DONE” in top right corner

Magic! Flow added another row

Note that timestamp records UTC time.

Sharing with others

Apparently, it is not easy to share flow with someone. Imagine your wife has a Microsoft Account and you want to share Flow with her. I couldn’t do that, neither by adding another owner nor by managing run-only users.

The only way I believe will work is exporting flow and importing in her MS Flow account.

In that case, Flow from her account should be connected to my OneDrive. Such setup will potentially lead to an additional request for credentials for flow connection when I change MS Account password. Any better ideas?

I believe that based on this guide you can make your own even better solution.

Thank you for reading!