This was done on the spur of the moment. I was given a template to complete the expenditure budget and track it. Since the Statistics of Singapore wanted to know the home expenses, I diligently did the tracking for the stipulated 14 days.
So, my handwriting isn’t the best, hence, I resort to making a tracker that I do best in, using Google Sheets. While MS Excel would have done well, it would mean that I would not be able to use the tracker unless I can access the same device. Although I can also do it via MS Office 365, not that I am not subscribed either.
I was using just a few columns and then thought of the different currencies. What if I were to travel and spent the money in that currency? How would I know what to do about the conversion rate? So, my columns evolved and included my favourite way of converting the data. Since I was not familiar with all the functions, I had to search for the relevant formula that I need to use.
The formula below uses Google’s functions to search and convert the currency value. I wanted to reference to the inputs instead of keying the formula each time.
=if( OR(ISBLANK(AmtCellSpent)=TRUE, ISBLANK(CurrencyFrom)=TRUE, ISBLANK(CurrencyTo)=TRUE), "", IFERROR(AmtCellSpent, GOOGLEFINANCE("CURRENCY:"&CurrencyFrom&CurrencyTo)*AmtCellSpent) )
I use a dropdown to select the currency, payment mode, and item category. I would have preferred the date picker to have a dropdown arrow instead of having to double-click to have the date picker displayed though.
The next step would be to explore how to incorporate Apps Scripts into Forms and use this in a more UX manner.