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)

September 15, 2020 update: Amazon has changed the way they share order-history CSVs. We have updated the Amazon order-history importer to use a different order-history report from Amazon.

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
  • 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 Money Labs Add-on

Install the Tiller Money 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 Money 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 or another source
  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) (You can turn the filter off after review)
  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.

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 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 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.

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 transitioned 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.

I can’t find Amazon’s Order History anymore.

You might have read in this community that Amazon has discontinued ready-access to CSV order history reports. On September 13, 2020, The Tiller Money Labs team has updated the import rules to leverage a different order report that provides similar functionality. Details are available here.

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 tool in the Get Help > Workflows & Tools 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.

4 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

Hi @randy! also a big fan of tiller. I accidentally posted this in the old standalone Amazon addon article earlier today but was pointed here. I actually was using the Tiller Labs Add-on (not the standalone) when I wrote this:

“Hi @randy, I have a personal and business amazon account. Worked great for the personal, but as noted above, didnt work for the business export. I took the from Personal Items Report export that worked and edited the business export to mimic the exact data / template structure / column headers. However on upload it just gets stuck on the processing the data step (with the dots gif).I cant figure out why it wont work. I imagine something simple i’m missing. Any chance you would be willing to peek at my CSV to see what is causing the snag? If you can help figure it out, I can write a quick little script to convert business exports to personal export template for others with the same issue to use until amazon business is supported. Cheers!”

Sounds like I’ve been trying to the same thing as @Mariusz. Would love to get this working as it would save me a ton of time. If you’re willing to take a quick look at the csv file im trying to import to see why its not working I’d truly appreciate it and would write a script to help others in the same predicament. Thanks!

I haven’t built an importer for an Amazon business export yet. It probably isn’t a huge project and I’m willing to take a look at the feasibility in the coming week, @ellisar.

The file you shared looks really similar to the Amazon Personal export. I assume that is the version of the CSV that you massaged to make it look like an Amazon personal file and not the original business export. Can you confirm this?

If you’re willing to share a bit of an Amazon business export, I’ll have a look into working up a dedicated import rule set. Just send me the first few rows (especially the header) via DM in this forum. Feel free to scrub any personal info.

Best,
Randy

P.S. In the interim, if you want to ingest the file you shared, just add a column called Group Name in AJ1 and try re-importing.

P.P.S. I deleted the attachment from your post for your privacy.

@randy That is great to hear. I appreciate your willingness to look into it so soon.

That is correct, the version I sent you was an Amazon Business Order Report Export. Almost all of the data was exactly the same with just slightly different abbreviations for column names (plus the business export had a lot more columns to sift through to find the required ones). Pretty much only had to change column names to Amazon (Personal) Items Report names and concatenate 2 cells into 1 for 1 column.

Will DM the Business export now. And awesome! Will give the Group Name column addition a go in the morning – will report back! Truly appreciate your help with this.

Cheers,
Adam

Why does the Amazon CSV importer clear the Account name column of the original credit card transaction and then it fills in the account name pulled from the Amazon report which does not really map to anything? I would have assumed to keep the original Account name for the actual credit card transaction (the negative charge) and then put something like a placeholder for account for the additional [Amazon Item] transactions.

Doesn’t this mess up account based transaction filtering?

Mark

I just noticed that when I ran the Amazon importer via the Tiller Labs Add-on, it somehow deleted/removed the original credit card line item. All that I have remaining is the positive [Amazon Order] entry and the negative [Amazon Item] entries. I’m missing the original entry which is inconsistent with the example above. Now I think my credit card entries won’t correctly tie. Please tell me that this is not the case?

Mark

The importer does not delete any transactions, @mark.chien . The [Amazon Order] rows are “offsets” meant to negate them. If the original transactions were deleted— again, they are not— the math would break down.

Is it possible you have a Filter active in your Transactions sheet? Are the cell label bars green?

Good question, @mark.chien. Account fields that could exactingly align to your Tiller data do not exist in the Amazon CSV.

While the CSV importer workflow could gather all internal account data from your Tiller Money spreadsheet, find the closest account-match to the CSV data, and use those values to populate new rows, it currently does not.

Long story short, you will need to do some manual data cleanup to perfectly match up Account name/number/institution/ID data for some forms of “account-based transaction filtering”.

Thanks for the quick reply. Think I finally figured out what happened. Thanks for clarifying how the add on works. Basically, it is just importing the raw data from the Amazon order extract and doesn’t try to do any kind of matching of existing transactions. The main reason why I could reconcile is that I noticed what is imported from the Amazon Order history and what shows up on the credit card transaction is often not 1:1. Here are some findings:

  1. The Amazon order history does not factor in certain discounts like “Subscribe and Save”. So when I get a 5% off, my credit card transaction has a lower charge than what is imported from the Order history. This is a BUG in Amazon’s feed. I don’t know if this is an issue with other discounts like coupons, etc.

  2. The Amazon Order feed has the wrong ship date in some circumstances.
    For instance, the feed said the item shipped on 3/7 but on the website, the item actually shipped on 3/14 (this was a subscribe and save item). The credit card is charged on the actual ship date 3/14. I think subscribe and save really mucks with this feed.

  3. When you use a payment mechanism like an Amazon gift card for full or some of the order, there’s no credit card transaction or it’s much less but the Amazon Order import will include the full amount. In order to properly fix this, the user needs to remember to create a “transaction” in tiller when a gift card is added to the Amazon account.

So in short, it feels like a good way to model this is to create a notion of an “Amazon” account (i.e., add a manual account) and have all the Amazon Items/Order be transactions in this account. So credit card payments to “Amazon” would be transfers to this account. When redeeming gift cards, these should be treated as “deposits” to the “Amazon” account.

In the end, things won’t perfectly balance but you do get close. Definitely a fantastic feature but it will change your Amazon buying behavior somewhat to make things tie. :slight_smile:

Mark

I realize, this is actually not possible due to some limitations in how the Amazon Order feed works.

In building the tool, I had originally hoped to find matching credit card transactions and expand them into their line items. Ultimately, limitations in Amazon’s data set presented too many challenges to do that properly. So I simplified the approach to add in the offsets which is fairly accurate in most cases.

Your manual Amazon account approach is interesting. Do you find the existing workflow is compatible (i.e. with minor manual cleanup)?

Thanks for the feedback, @mark.chien.
Randy

I think you did all the right things. The simplicity of just offering a simply import of the Amazon order information and modeling it as an offset makes sense. I think the main issue here is that Amazon’s order extract has bugs and it’s not clear how well maintained it is. It’s a super useful feature but I worry at how “accurate” Amazon will be at taking order level discounts/changes and applying them at the item level. It’s a hard problem when you have credits applied, item specific discounts, gold box, etc. etc.

The fact that you’ve made this possible is pretty fantastic. Amazon has always been just a black hole for my finances and they’re not designed to help you understand how you’re spending with them.

I haven’t tried the account approach yet but in theory, it should work. Just treating at the category offset level works as well since the most important thing is to capture the Amazon spending properly at the category level.

If you see in the Amazon CSV any underutilized columns or other ways to make improvements to how fields are mapped into Tiller spreadsheets, don’t hesitate to let me know. It’s possible there are clues in there that I’m not using.

Thanks for the feedback.

In case of any interest to newer folks working on Amazon autocat (like me), here is how I’m filtering and assigning a few discrete recurring Amazon charges and cleaning up the messy descriptions. (“Amazon Prime annual” subscription charges are discretely described by Amazon and I categorize these as a business expense. “Amzn Digital” in my case means Kindle books almost always. The 3 text strings here pick up about 7 or 8 different odd-looking text descriptions I get from my ordinary Amazon Item purchases.)

Of course, each ordinary Amazon Item then must be individually categorized which is the whole point: e.g., "dog collar " goes to “Pets”. But this gets you started with a clean description and some easy categorizations.

My Amazon autocat approach: columns shown here are Categories, Description Contains, and Description (rename)

By the way, the importer is just a terrific tool/bot Randy!

1 Like

I’ve got something similar in my AutoCat rules though not quite as thorough. Well done.
Thanks for documenting this, @Larry.