Expensify.org is accepting proposals for new campaigns. Submit yours here by April 30th to receive up to $100,000 in funding for campaigns dismantling injustice related to: Climate, Homes, Hunger, Reentry, or Youth.

Customized CSV download from Card Reconciliation

nhoffman94_tobnhoffman94_tob Expensify Customer Posts: 34 Expensify Newcomer

I would like to be able to pull my custom csv report from this reconcilation page. It only gives me the option to pull the standard csv version. I need this so I can more easily import data directly from this page to our accounting system.

I know I can go pick through on the expenses tab and pull my customized report that way, but it would save me so much time if this was an option.



1 votes

Understanding the Issue · Last Updated

Comments

  • Conor PendergrastConor Pendergrast Expensify Success Coach - Admin, Expensify Team Posts: 127 Expensify Team

    Hey @nhoffman94_tob, good to hear from you. It would be helpful to hear a little more about your process, so that we can see what we can advise. At what point are you getting into this Settlement reconciliation section, and what are you aiming to have finished when you're done?

  • nhoffman94_tobnhoffman94_tob Expensify Customer Posts: 34 Expensify Newcomer

    Whenever a settlement hits our bank account (daily), I need to export that exact data into my custom csv and import it into our financial system. I have a custom export format that allows me to import the data exactly how I need it formatted for the other system.

  • Karisa LattaKarisa Latta Expensify Success Coach - Admin, Expensify Team Posts: 134 Expensify Team

    Ah, thanks so much!

    We don't yet have a custom export option for that Reconciliation feature. However, if you're a savvy Excel/spreadsheet user, you can utilize our current Download CSV option and run macros or snippets to manipulate the data automatically in Excel or your favorite spreadsheet program.

    If you have an accounting package we directly integrate with, like QuickBooks Online, we can actually handle uploading your settlement data automatically for you via Automatic Reconciliation.

    This is a super neat idea, so thank you so much for proposing it! Please encourage your network to vote for this idea so can consider building it.

  • nhoffman94_tobnhoffman94_tob Expensify Customer Posts: 34 Expensify Newcomer

    The only problem with that, is that the default export doesn't export all the information I need. It only gives me a few key columns that I need. We have a lot of dependent tags set up and I need to know the information my custom spreadsheet will give me.

    We are unable to use quickbooks, and need to use Unanet to stay compliant for government contracts that we work on. They are working on a possible integration, but because of the specific way we have to put information into Unanet, I'm not sure if the integration will work for us. So this custom export feature from the reconcilation area would be so so helpful.

  • Karisa LattaKarisa Latta Expensify Success Coach - Admin, Expensify Team Posts: 134 Expensify Team

    Ah, I see what you're looking for. You're looking for a download that includes all expense line items and their coding PLUS the related settlement lines. We do show high-level expenses-settlements and batch IDs in that Download CSV option but definitely not the expense coding bits.

    You could still utilize a macro in Excel but you'd need to combine two exports - the one from the Settlements page and an export from the Reports page. This can get a bit hairy if you don't have the Excel macro skills needed.

    I'll forward this idea to the team! Keep encouraging folks to vote so we can get some real traction on this.

  • nhoffman94_tobnhoffman94_tob Expensify Customer Posts: 34 Expensify Newcomer

    I can look into this option, but the less I have to manipulate the data, the more accurate it's going to be.

    And the goal is to try and make this process quicker, I'm not sure if that method would be faster, but I can look into it.

  • Conor PendergrastConor Pendergrast Expensify Success Coach - Admin, Expensify Team Posts: 127 Expensify Team

    Ok, thanks Nicole - I appreciate you walking us through all of the details here!

  • Ted HarrisTed Harris Expensify Success Coach - Admin Posts: 319 Expensify Team
    edited February 9

    Hi @nhoffman94_tob, we'd actually toyed with including some more identifying expense information in this export, or in our detailed export previously but decided against it. The reasoning is simply that this statement isn't actually an accurate representation of approved transactions, just of those that were pending on each day, and thus billed by Expensify.

    There could be many transactions on here which never posted (but you were charged and then reimbursed for). They could be subsequently split into different transactions. They could be settled as a single transaction, but have been posted and thus, billed by Expensify on multiple different days (as is the case for meals and their tips).

    The statement dashboard really just tells you what you were billed for on a specific day at that point in time. The Reconciliation Dashboard tells you what is settled and approved.

    The only thing you could use to link the two is the {expense:id} formula when creating a Custom CSV export. For any transactions which haven't been split, or reverted by the merchant, or refunded by either Expensify or the merchant, etc - this will match the "Transaction ID" from the Statements Dashboard CSV export.

Sign In or Register to comment.