First of all, I am in Kindergarten when it comes to spreadsheets, that’s why I chose Tiller. Tiller is obviously not for Kindergarteners.
Here is how I am using Tiller and what I have done:
I am using Tiller Foundation Template on Google Sheets. I use a MacBook Pro.
I am using Tiller strictly to input expenses and create reports based on categories (mainly for tax purposes, but also for personal and business expense tracking)
My Tiller spreadsheet is not linked to any accounts because my bank does not support it (small usage bank, I spoke to the bank and they recommended manual entry, which is fine for my purposes)
I have connected the account through the Manual Account process (again not linked, but did input my account information) I PUT THE BALANCE AS “0” because I do not care about balance, but I do care about reports and categories. I am unsure if this screwed up my template.
As I have been trying to enter historical data (yes I watched the videos and read the articles), here are my issues and what I have done to resolve them:
My e-statements are only available in PDF form for historical data (Jan-March 2022). I called the bank on this too, and they said that is the only form they have for old statements. It has been a nightmare but I got them entered through lots of trial and error. I followed the guide for manually importing historical data once I got them out of the PDF.
For not-so-old statements (April-December 2022) I was able to get them in a text file which I was able to then put into an Excel file and then transfer to a worksheet (following the Tiller guide on manually importing historical data), and eventually into the Tiller Foundation Template.
My biggest issue is I am obviously insane for spending all of this time and effort trying to make things “easier” when I could have already been done if I would have done it by hand🤦♀️
** To make it worse, after all of that effort, some of the “amounts” that I pasted into the Foundation sheet are not showing up as EXPENSES. Because of this, when I try to run reports or make a pivot table, nothing is accurate! You can imagine that at this point I want to pull my hair out!!**
Here is what it looks like in my spreadsheet in the “Amounts” column: (I added a line to separate them in the sheet, just so I could keep track of the error)
You can see that some of them have the “-” sign in front (recording as expenses) and some do not (which are recording as income). They are all expenses and the categories that the amounts are associated with are listed as “expense” under the “Type” column in the category sheet.
-$140.00
-$175.00
-$42.65
-$114.80
-$15.55
$13.18
$34.91
$300.00
$1.00
$1,804.21
$30.00
Here is what I have done to rectify this:
I have categorized every transaction and they are all listed as “EXPENSE” on the category sheet under “Type”
I use “paste special, values only” when pulling in the “amount” data from the worksheet.
I followed the guide on “Data Validation” for categories, just to be sure it wasn’t the issue.
I have made sure the “Amount” category is set to “currency”
I also use “Auto Cat” and update frequently.
Questions:
Are the categories linked to the transactions? If the category is an “expense” type, doesn’t the transaction column “know” it should be subtracted?
Did I screw this up from the beginning because I entered “0” as my balance on my manual accounts?
*Should I even be using Tiller for my purposes? (I feel like it will be much easier to input the past 90 days’ transactions since I can actually get them in a CSV file, but I don’t want to continue to waste my time if Tiller can’t be used to just track expenses and run reports).
Thank you for any help. Please answer like you are talking to a five-year-old.
You’ve joined the club! I understand your pain. Anyone who tries to automate things runs into this dilemma – there’s probably a named axiom for the pivot point between making things simpler or more complicated.
Normally in Tiller, the Category is not known until and unless you:
• manually determine it,
• use AutoCat to set it via rules, or
• import data in which the Category is already determined.
Normally in Tiller reports, an entry is determined to be an Expense by its Category, and that Category is an expense Type.
Do I understand correctly from your above description of the Amount column that you have some expenses that are listed as positive numbers when they should be listed as negative numbers? Or am I misreading?
To help you further, could you please confirm if the problem is with (some of) the values in the Amount column, or the values in the Category column. Or both?
Seems like you are close! Just need to correct some data and you’ll have a solid foundation to work from.
Thank you so much for responding and validating my pain😂
Yes, there are numerous expenses that are listed as positive numbers instead of negative numbers. In the amounts column, they do not have the minus sign in front of them.
All of my categories have either been manually determined or have set rules by AutoCat. All categories are “Expenses” under the “Type” Column, but do not show up as expenses (negative numbers) in the “Amounts” column in the transaction sheet.
Also, I realized that even some of the numbers I entered using the “Manual Transaction Tool” are showing up as positive instead of negative numbers in the “Amounts” column.
I have cut and pasted another example:
From my Transaction sheet:
Description Category Amount
COSTCO GAS Travel $59.00 (notice there is no minus sign, so it is registering as income rather than expense)
Here is what it looks like in the Category Sheet:
Category Group Type
Travel Business Expense (So Travel is categorized as Expense)
And here is one that is exactly the same, but is listed as a negative number (Expense) , which is correct:
COSTCO GAS Travel -$37.06 (notice the minus sign, so this is correctly displaying as an expense)
I’d like to suggest a method for correcting the positivity of your expenses. This will be a one-time fix to the data in your sheet. Should you manually enter or import data in the future, you can ensure the negativity of your expenses then – or use this process to fix.
Filter your Data - To ensure that you only convert positive numbers to negative, I suggest that you either filter or sort your entries so that only positive numbers are selected and changed.
Select and Copy - Select and copy all the positive expense entries which need to be signed negative. Be sure to leave these cells selected so you can paste in the fixed values.
Create Temporary Sheet - In a blank sheet used temporarily for this process, paste the cell values (.
“Flipping Formula” - To the right of the top value, create the formula which takes the value of the cell, multiplies it by -1 (so the amount doesn’t change), but the “sign” changes to negative. Copy the formula to the rest of the cells.
=A1*-1
Note that intentionally “accidentally” included a few negative numbers at the bottom of the list to show that any negative numbers will be turned positive – so be sure to include only positive numbers.
Copy Results - Now copy all the cells and go back to your Transactions sheet. [Of course, be sure not to change the order of cells in either sheet until you are done so the right values get put back with their associated transaction.]
Paste Special - With your original selection still selected, use Paste Special in the Edit menu and choose Values Only. This will paste the now negative values over the originally positive values. See screenshot.
[Note that in the screenshot, I pasted into column E for example, but you’ll paste them back to the Amount column.]
And you should have expensed expenses.
Is this kindergarten enough? I’m sure you’ve got this!
If you’re seeing transactions coming in with the incorrect sign (they don’t have the minus sign) then we may be able to work with our data provider to correct that issue so you don’t have to manually correct each entry, though @ThriftScout’s steps are AWESOME!
As for the manual transaction, just make sure you’re switching the dropdown at the top of the manual transaction sidebar to withdrawal instead of deposit and it should input it as a negative number.
OH MY GOSH, IT WORKED!!! :tada
Thank you so much!! I feel like I just graduated to 1st grade!!
I appreciate your help and thorough explanations! Thank you for taking the time to help!!
The transaction column and category column are independent. And that’s actually a good thing.
There are cases when an Expense transaction might be positive. Let’s say you bought $200 of concert tickets. That would appear as a negative $200 (-200) Entertainment expense.
But then someone reimburses you for half of the tickets. That reimbursement would be positive $100 to the Entertainment expense. You only spent $100 on Entertainment. -200+100 = -100.
There can also be negative Income transactions.
True these are not as common, but they are possible.
You’ve joined the club! I understand your pain. Anyone who tries to automate things runs into this dilemma – there’s probably a named axiom for the pivot point between making things simpler or more complicated.
Note that intentionally “accidentally” included a few negative numbers at the bottom of the list to show that any negative numbers will be turned positive – so be sure to include only positive numbers.
And you should have expensed expenses.
I’m sure you’ve got this!