How To Import CSV Line Items with the Tiller Labs Add-on

Overview

The Tiller Money subscription service links financial transaction & balance data to your spreadsheets daily. 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 Labs add-on’s Tools menu will help you efficiently and accurately import data to your spreadsheet.

The Import CSV Line Items workflow was ported from the previously-standalone Amazon Line Item Importer add-on which will be deprecated.

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

What Are the Steps?

Download and Install the Tiller Labs Add-on

Install the Tiller Labs 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

  1. Open the Tiller Labs add-on
  2. Navigate to the Tools menu
  3. Click on Import CSV Line Items
  4. Review the instructions in the sidebar relevant to your data source
  5. Click the “Upload CSV File” button
  6. Navigate to the CSV file you downloaded from Amazon
  7. Use the “Filter Date Range” controls to filter the data source’s date range (optional)
  8. Review the offset categorization dropdown (relevant to only Amazon, Venmo & Paypal imports)
  9. Check the “Filter transactions after import” checkbox if you’d like to show only newly imported uncategorized rows after importing (optional)
  10. 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 :wink: — 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.

Frequently Asked Questions (FAQ)

Is the Import CSV Line Items workflow supported by Tiller?

Tiller Labs 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 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” category representing the cost— tax and shipping— of purchasing from Amazon.

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.

image

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 :wave:— 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.

How is the Import CSV Line Items workflow different from the standalone Amazon Line Item Importer add-on?

The Import CSV Line Items workflow was ported from the previously-standalone Amazon Line Item Importer add-on which will be deprecated. The Tiller Labs team believes users will be better served by consolidating the functionality into a single add-on.

2 Likes

Huge tiller fan here.
This looks great. Is there any way for this tool to work with any bank? Isn’t it all about identifying and matching the right column headers?

I had a solid workflow but once Yodlee stopped supporting UK (and many other) financial services it all broke.
For now I’m trying to use formulas in G Sheets to trim and re-format the CSVs to match the tiller layout/format and paste but this solution is rather slow.
I’ve also seen a python script (htt*s://d.pr/X5hjpA) the would format the csv data from Apple Card to work with tiller.
The code probably needs to be adjusted as probably has the potential to work with any bank/financial institution?

Glad to hear you’re enjoying Tiller, @Mariusz.

The tool can work with other banks but currently works with a preset list of remapping rules for known import types. (It is not currently programmed to recognize column names and dynamically remap them.)

I think we’ve got Apple Card working though sometimes the outputs (and thereby unrecognized) are slightly different when using them internationally.

I hope we are able to support you soon in the UK.

Randy