CSV uploads auto-merge with SmartScanned receipts

Cortney Ofstad
Cortney Ofstad Expensify Success Coach - Admin, Expensify Team, Expensify Student Ambassador Posts: 173 Expensify Team

Update: Spreadsheet uploads for importing expenses will now automatically merge with SmartScanned receipts.

This means you won’t need to manually merge SmartScanned receipts and manually imported expenses together to clear duplicates — just let us take care of that for you!

Learn more about the spreadsheet upload option here, or check out our SmartScan help doc to read up on how to SmartScan your receipts.

Comments

  • janne_b
    janne_b Expensify Customer Posts: 3 Expensify Newcomer
    edited November 2017

    Hi @Cortney Ofstad

    Been waiting for this. Great! Please help confirm two things:

    1) Does the order of things matter?

    • i.e. can we upload a card-statement (as .csv) first, then populate the receipts via the iOS app?
    • and vice versa..
    • or after snapping a few photos via the app, then upload the .csv which then can auto-matche already existing receipt-scans row-by-row (I assume matching on the amount field)?

    2) Can the import feature now match using an original currency field(s)?
    Let's say we smartscan a receipt from europe on 14 Euro,

    • the card-statement .csv contains additional columns currency and amount stated on the receipt (in the foreign currency), i.e. "EUR", "14"
    • So instead of trying to translate 14 euro to the account currency, we want to match the receipt using the original currency column so they're 1:1
    • The of course, the expense report will use the actual amount charged to the account/card, i.e. $13.91 to avoid any currency conversion differences.

    Is this doable via the updated upload feature or perhaps via the API!?

  • rPE
    rPE Expensify Customer Posts: 1 Expensify Newcomer

    I would also like an answer to your question!! It tried using this feature and it does not work! I smart scan and then import my CSV and the duplicates stay in the expenses. What am I doing wrong?

  • John Schuster
    John Schuster Expensify Team Posts: 127 Expensify Team

    Hey there @janne_b! Those are great questions!

    1.) Does the [upload] order of things matter?

    TL;DR (Too Long; Didn’t Read): The order that you upload SmartScans/CSV shouldn’t matter. All that matters is the state of the expenses when merging, and whether or not the transaction data is “close enough for rock and roll” to be matched.

    The State of the Expenses:

    In order for expenses to be merged, both expenses will need to be in an Open (unsubmitted) state. If an expense has been submitted and you’d like to merge it with another expense, you’ll need to retract the submitted expense first.

    Close enough for rock and roll:

    SmartScan status, Transaction Amount and Date, and expense-types, all play into whether or not a transaction will be matched for merging.

    First, has the SmartScanned expense actually completed SmartScanning? If not, let SmartScan complete and try again! We can’t match data if we’ve not read it, right?

    Second, do the expense amounts match, and is the SmartScanned receipt dated at most 7 days before the card expense? We should be able to account for a change in currency. We also know that the card's posted date from the bank may be different than the date on the SmartScanned expense. We should be able to "fuzzy match" these.

    Third, are the expense types the same or different? SmartScanned receipts are eligible to be merged with credit card expenses and expenses uploaded from a spreadsheet.

    2.) Can the import feature now match using an original currency field(s)?

    We're pretty smart cookies when it comes to handling two currencies, and we should merge these based off the daily exchange rate for the date of the transaction, as long as the converted rates are within +/- 5%. To test this, use this historical exchange rate tool, convert both amounts to USD, and see if they are within 5% of one another. If they aren't, we won’t be able to merge the expenses, as our confidence levels for pairing the expenses won’t be high enough. If that happens, you’ll want to manually merge these expenses.

    Does that make sense?

    These and many more topics are covered in detail in our Expense Merge Troubleshooting doc. If you find your imported CSV data isn’t merging as expected, I’d suggest taking a quick look there to see if your expenses fall within one of the known reasons for not merging. However, if you find yourself stumped, give us a shout at help@expensify.com!

  • janne_b
    janne_b Expensify Customer Posts: 3 Expensify Newcomer

    Hi @John Schuster

    1) thx for getting back. I've been experimenting with this, collecting receipts via the iOS app. At the end of the month, I upload the .csv.

    • Auto merging doesn't happen, the amounts and dates are 1:1
      any ideas how to trouble-shoot? any ways to trigger a new "auto-merge"?

    • you mentioned the expenses needs to be "Open (unsubmitted) state". I believe you mean "unreported" (white), correct? where "open" (blue) it's already tied to a report, so that will not work?

    Side-note:
    it would be very convenient if the csv import got created as a report (basically letting you name it during the manual upload process steps. Currently it just creates a bunch of expense rows which is unwanted if you're working with multiple cards and uploads (as you'd have to click one by one to group them into a report for that specific .csv manually)

    2) Thanks, but what I mean here is to keep the base currency coming from the uploaded card statement.

    Let's say it manages to auto-match, the amount should be what was actually debited the card, not the foreign currency closely matched.

    Most banks / card providers gives ju additional columns in the statements, one with a standard currency code "EUR", "DKK" etc. And one with the foreign amount (that would be the amount on the receipt, i.e. "123 DKK", while on the last "amound column" it will read the actual sum that your bank charged to do the currency translation, say 19.28 USD.

    This way, allowing to import and map these two additional columns the smartScan feature could match 1:1 with the receipt and make everything way smoother and fool proof!

    best J

  • JackJ
    JackJ Expensify Customer Posts: 4 Expensify Newcomer

    BUMP!

    Hey Cortney and John,

    It would be great to get some answers to some of these questions, I'm experiencing the same as janne_b - I have uploaded expenses and our .CSVs and Expensify isn't matching them, even the obvious matches (i.e. same date and value, etc).

    Am I missing something? Is there a way to manually push Expensify into looking for matches?

    Thanks
    J

  • benedict
    benedict Expensify Customer Posts: 7 Expensify Newcomer
    edited December 2018

    Cortney - thanks for the update.

    What happens to the SmartScanned expenses if the csv import is deleted (from Settings > Your Account > Credit Card Import) ?

  • John Schuster
    John Schuster Expensify Team Posts: 127 Expensify Team

    @janne_b and @JackJ First, let me apologize for the severe delay in getting back to you on your questions! I'm afraid I had mucked up my notifications here in the forum and had missed your responses.

    Let me start by digging into @janne_b's questions as they seem to address some of @JackJ's needs as well:

    First, if auto-merge doesn't happen on import, the only way to merge the expenses would be to manually merge them. As far as why they didn't merge, that requires a bit more digging. If you still have expenses that are not being auto-merged, would you be willing to shoot me an email with an example at jschuster@team.expensify.com? I'd love to address that, as well as follow up here so the community has an opportunity to see an example.

    Regarding my comment about "Open" expenses – yep! You got me! I should have clarified that those expenses would have needed to be in an Unsubmitted state, which can include expenses that have been placed on a report but have not been reported (i.e. in an Open state).

    However, there's been a recent change that makes this no longer true! A recent update has now made it possible for SmartScanned expenses to be merged with reported expenses. This includes expenses on Processing, Approved, Reimbursed and Closed reports.

    About the currency conversion: Sorry I missed the mark before! When an expense is placed on a report with a different output currency than the original expense, the report will show two columns indicating the expense amount; the first being the original expense amount (in foreign currency), and the second being the converted expense in the policy's output currency.

    Does that achieve what you're after? Let me know if I'm still not quite getting it.

    @benedict Great question! Deleting a CSV import will delete any unsubmitted card transactions (including those on Open reports). On the flipside, any expenses that have been submitted will not be deleted if you delete the associated CSV.

  • janne_b
    janne_b Expensify Customer Posts: 3 Expensify Newcomer
    edited December 2018

    Hi @John Schuster

    Thanks for getting back. Please see comments below:

    1.

    First, if auto-merge doesn't happen on import, the only way to merge the expenses would be to manually merge them.

    I have a suggestion on how to make this process way smoother and efficient.

    • Simply allow clicking on an imported row (in the report-view) that is missing a receipt.
    • Then make it possible to pick from existing smartscans / manually uploaded receipts that's available

    Right now you can only choose to upload a new receipt which is unwanted if the receipt is already there. I know we can merge by hand, but this is more tricky and less intuitive as you normally work within the report, not in the bulk-view where receipts are mixed with imported rows.

    2.

    Regarding "Open" expenses vs. Unsubmitted state

    Let's triple-clarify this. So when we upload a .csv under
    Settings > Your Account > Credit Card Import -> Import Card/Bank
    then what I usually have to do is to

    • Under Expenses: filter by choosing the specific card
    • then limit the rows shown by filtering on date (this to avoid rows from other imports to become part of my last "card statement import"
    • now I select all rows and create a new report so that it matches the rows I just imported in the first step.

    comment: why don't this happen automatically!?
    wouldn't it be neat if the .csv import is seen as a card-statement == a new report, just use the filename as the key (or offer to create the "report & name" already during the upload step. This would be awesome as it eliminates the need for the manual consolidation of the imported rows.

    once above is done the pool of smartscans should be merged to that report (that now will be in it's open state), correct?

    3.

    About currency conversion & ...the report will show two columns indicating the expense amount; the first being the original expense amount (in foreign currency)

    This is a great addition, but..!! The .csv import doesn't let us map the additional 'foreign_currency' vs 'expense_currency' during import. I just tried this and only options in the select-menu under the "Import Transactions" modal pop-up window is:

    • merchant, date, amount, category & tag

    Would be great if we could see a new option for "amount in foreign currency" as most card-statements include both.

    This way the auto-merge would be even easier for you guys to get a higher match score as the smart-scanned receipt could be matched 1:1 with the foreign-currency column imported.


    Please comment.

    thx!