Overview
Tiller automates your daily spending, transactions and balances to your Google Spreadsheets, but manual imports of CSV files may still occasionally be required when:
- Migrating data from other platforms (e.g. from Mint, YNAB, Personal Capital)
- Importing from unsupported data sources (e.g. Amazon)
- Importing from supported sources with limited integrations (e.g. Venmo, Paypal)
For the situations like those above, the Import CSV Line Items workflow in the Tiller Community Solutions add-on’s Tools menu will help you efficiently and accurately import data to your spreadsheet.
How Does the Workflow Help?
The Import CSV Line Items workflow makes importing easier by:
- Recognizing & validating your import data source
- Remapping fields to match up with your Tiller spreadsheet
- Creating offsets to zero-out credit card charges from your feeds
- Itemizing orders into discrete line-items to facilitate accurate categorization (Amazon imports only)
- Tracking imported data and preventing the import of duplicates (not all data sources)
- Storing searchable metadata in the Transactions sheet
- Providing one-click filtering of uncategorized imported line items
Who Is This For?
This workflow will save time and improve accuracy when tracking how money was spent for personal-finance & small-business users who regularly transact on the following services: Amazon, Venmo, Paypal, or Apple Card.
The workflow will also help migrating users who wish to move data from another personal-finance platform such as Mint, YNAB or Personal Capital.
What Data Sources Are Supported?
The Import CSV Line Items workflow will recognize the following typess of CSV files:
- Amazon purchases (USA only)
- Amazon refunds (USA only)
- Apple Card purchases
- Mint transactions register
- Paypal transactions
- Personal Capital transactions register
- Venmo transactions
- YNAB transactions register
- Basic Bank CSV
Basic Bank CSV
Why use this workflow?
- If your account is not supported for automated feeds from Tiller Money.
- If there is a temporary outage for your institution.
- You want to get historical data from before the first automated import via Tiller Money Feeds
You can make a copy of the Basic Bank CSV Template here from the file menu.
A few tips when using this workflow:
- You can paste the data from the CSV you export from your bank into this Basic CSV template for the Date, Description, and Amount columns.
- We don’t recommend using Categories provided by the bank as they may not match the categories you have set up in your Google Sheet powered by Tiller Money.
- You can use the Config tab in this template to list out the Institutions, Accounts, and Account numbers and then select one at a time from the dropdown on the Config sheet. These selections are being pulled into the Transaction Prep sheet in this Basic Bank CSV template sheet.
- Feel free to customize this template to meet your workflow needs (e.g. if you want to do multiple accounts at a time you can clear out the references to the Config sheet in the Institution, Account, and Account # columns), but the template will only import Date, Description, Amount, Institution, Account #, Account and ignore other columns.
- You can record a Macro in Google Sheets to transform your data and format for this CSV file type more quickly.
- You can use the Basic Bank CSV template Google Sheet as reference and prepare your CSV file using Excel. The intention is that it gives you a starter for how you can re-format a CSV export from your bank for use with the importer tool.
- If your bank CSV export includes a transaction type column using “credit” or “debit” and absolute values rather than -$ amount values you can parse and transform those amounts in a separate column using a simple formula =if(F2=“credit”,E2,-E2) where F represents the “transaction type” column and E represents the amount. Keep in mind that the way a checking account and a credit account handle transaction types may differ (e.g. what’s consider a credit/debit.
What Are the Steps?
Download and Install the Tiller Community Solutions Add-on
Install the Tiller Community Solutions add-on. During install, you will need to accept the add-on’s scopes request.
Categorize Related Charges (Amazon Only)
Be sure you have consistently categorized the existing related charges in your Transactions sheet (i.e. the credit card charges added by Tiller Feeds) with either the category Amazon or Transfer. This step is important to ensuring any new [Amazon Order] rows (added by the add-on) will offset properly with your credit card charges.
Validate Your Spreadsheet
When opened, the Import CSV Line Items workflow will check if the active Google Sheets spreadsheet is compatible with the workflow. If the spreadsheet is compatible and required columns are missing, the workflow will ask for permission to add them.
Import from Your Data Source
- Open the Tiller Community Solutions add-on
- Navigate to the
Toolsmenu - Click on Import CSV Line Items
- Review the instructions in the sidebar relevant to your data source
- Click the “Upload CSV File” button
- Navigate to the CSV file you downloaded from Amazon or another source
- Use the “Filter Date Range” controls to filter the data source’s date range (optional)
- Review the offset categorization dropdown (relevant to only Amazon, Venmo & Paypal imports)
- Check the “Filter transactions after import” checkbox if you’d like to show only newly imported uncategorized rows after importing (optional) (You can turn the filter off after review)
- Review and click “Add to Transactions”
Complete the Categorization Process (Amazon Only)
Manually categorize all Amazon purchase line items with the “[Amazon Item]” prefix to their corresponding budget categories (e.g. Household, Kids, Pets, etc.).
What Is Line-item Expansion (Amazon Only)?
Instead of seeing a single charge from Amazon.com in your Tiller Sheet, this workflow:
- Creates new rows for each item purchased
- Pulls in each product’s name and details
- Shows exactly what you paid for it
For example, after running the workflow, instead of seeing a single charge like this:
You will see discrete line-items in your Tiller Sheet, like this:
You can then accurately categorize and track each item you purchased:
How Does Line-item Expansion Work (Amazon Only)?
Line-item Expansion via the Import CSV Line Items workflow enables discrete categorization of your purchase line items.
If you use Tiller Money Feeds, your Transactions sheet will already have (negative) charges from your credit card for your Amazon orders. Therefore, your cashflow will already accurately reflect your Amazon purchases due to the credit card charges from Tiller feeds.
So, the import workflow will add your transaction line items so that they are cashflow-neutral.
The magic of the add-on is that you will see discrete purchases that can be categorized without clumsy splits or time spent cross-referencing the Amazon site.
To add cash-flow neutral rows, the add-on creates two types of line items:
- Amazon Items are negative charges against your balance for individual purchases. They should be categorized manually for discrete tracking.
-
Amazon Orders are positive credits to your Transactions sheet that comprise one or more purchases. Order rows will be categorized as “Amazon” or “Transfer”— just be consistent
— by the workflow and should approximately* offset the credit card charges pulled into your sheet by Tiller’s feeds. (The negative credit card charges should also be categorized as “Amazon” or “Transfer” and will offset/balance with the order rows.)
Here is an instructive example:
If you add up the amounts of all the new rows inserted into your sheet by the add-on— the green and red rows— they will sum to $0.
Amazon CSV Import Exceptions
The Amazon import rules were built and validated using pretty vanilla transactions. The importer maps the CSV column Item Total to Amount in the Transactions sheet which can result in inaccuracies in certain situations.
@joern has graciously documented several common exceptions in this thread which are duplicated here…
Gift Cards
When you check out an Amazon purchase, you can apply Amazon gift cards which lowers the amount (if any) that is charged to your credit card, but the item report will still list the transaction for the full amount prior to applying the gift card. example: order $25 item, apply $10 gift card, credit card is charged 15. there is no way to see this in the items report CSV. If you download the orders & shipments report, in the Payment Instrument Column, it will list IF a gift card was used, but there is no way I have seen yet to determine how much was provided by the gift card. So the only automation here would be to ingest the orders CSV and throw a flag to trigger a manual adjustment if it detects a gift card.
Shipping Costs
If you don’t have Prime, or you have Prime and order something from a vendor who charges shipping on Amazon, you are charged a shipping charge. This is visible in the orders CSV in the “shipping charge” column, but not in the items CSV. You’d have to import both files and then correlate the charges. Or document/flag it as something that needs a manual adjustment.
Grocery Delivery Charges
While every single item from a grocery delivery order is listed in the item report, they delivery fee/tip is not. But it shows up as an Amazon Grocery charge on your credit card. Again, only manual adjustments possible here.
Frequently Asked Questions (FAQ)
Is the Import CSV Line Items workflow supported by Tiller?
Tiller Community Solutions add-on’s workflows and solutions are not officially supported by Tiller. They are supported by the Tiller community. Tiller may provide limited informal support in the community.
What if I import some of the same data twice?
Some data sources such as Amazon, Paypal and Venmo provide unique identifiers for transactions that can be used to filter source data so only new rows are imported. With these data source, the Import CSV Line Items workflow stores enough data in the Metadata column to uniquely identify data. If you import the same CSV file twice or import two CSV files with some overlap, the Import CSV Line Items workflow will ignore purchases that have already been imported.
Other data sources, such as Apple Card, YNAB, Mint and Personal Capital, do not offer uniquely identify rows. In these cases, care must be take by the user— e.g. with the date filter— to avoid reimporting rows.
Can I delete the rows marked [Amazon Order]?
Deleting the [Amazon Order] rows will affect your cashflow. As documented above, without the “order” rows, your Transactions sheet will double count purchases— once in the credit card charge (from Tiller Feeds) and a second time in the purchase line items from the workflow. The orders rows compensate for double counting and render the import cashflow neutral.
Why doesn’t my Amazon or Transfer category sum to exactly zero?
The order amounts will approximately balance with the credit card charges because Amazon does not publish precise tax and shipping values in the Items Report used by the workflow. (Amazon’s separate Orders Report does include precise tax and shipping values, but lacks the line-item purchase information.) Most users will end up with a slightly negative balance in the Amazon or Transfer category (whichever they choose to implement) representing the cost— tax and shipping— of purchasing from Amazon.
If there is a small net amount, it is more of an Amazon cost than anything else so using an Amazon to capture this residual amount may work well for you. Personally, on a macro level, I think using Transfer makes more sense and I’m not worried about a few dollars of unbalanced transfers.
How Should I Categorize Offsets? As Amazon? As Transfer?
The “Categorize transaction offsets” dropdown offers a choice between categorizing Amazon orders as Transfer or Amazon. They are both valid approaches.
Using Amazon uniquely and intuitively buckets the credit card charges in a way that is easily searchable. Alternatively, using Transfer aligns intuitively with Tiller’s Transfer concept— offsetting charges that net to zero— and cleanly removes the need for an extra category in your sheet (i.e. Amazon) that really needs no budget.
Personally— @randy here 
— I had been categorizing as Amazon and, with the start of 2020, decided to change to Transfer. The reason I chose to make this change is that our family is using the line-item/CSV importer to pull data from Amazon, Paypal and Venmo. I didn’t like the idea of creating a category for each data source— especially when each is just an unbudgeted staging area until the line-item offset is pulled in. For me, switching to Transfer felt leaner and more efficient.
What is the Metadata column for in the Transactions sheet? Can I delete it?
No. The workflow uses the metadata in the Metadata column to uniquely identify purchases (from some data sources). The column is required to avoid inserting duplicates.
Why doesn’t the workflow work with non-US Amazon account data?
The importer must recognize and validate the CSV data source to import data. At this time, import rules exist for US-based Amazon accounts. From what we’ve seen, the Items Report columns are different for other countries. We hope to add additional import rules in the future.
Will the workflow work with my Amazon digital orders?
No. Amazon does not include digital orders in its Items report.
Troubleshooting
If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this template in the Google Sheets category.
Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.
