Exporting Purchase Amount, Currency and Date

Kjacobsen

Hi,

I have a CSV import for my corporate credit card info, this seems to be importing the post date, transaction date, original and billed currency and amounts correctly. The import definition seems correct to me.

Running through all of the expenses, everything appears correctly. Just so you are aware, all of our transactions are billed in AUD and the reports currency is set to AUD.

As part of our expense process, I need a CSV with the original transaction date, currency and original amount, and the transaction description and the unmodified merchant information.

Looking at one transaction that was made in USD, {expense:amount:originalcurrency} was listed as AUD, and expense:originalamount} was the amount in AUD. It was showing the billed amounts and currency. I was expecting these to have the original currency and amount considering during the import, these values where imported with attributes "Original Currency" and "Original Amount".

After some work, and some help from support, I discovered that I can get the actual USD purchase amount using {expense:purchaseamount}, and I can get the original currency using undocumented field {expense:purchasecurrency}.

Unfortunately I still have 3 issues.

1. {expense:purchaseamount} is being listed with currency symbols - e.g. A$30.14. No amount of :nosymbol or :unformatted seems to remove it. This breaks one of our down stream processes.
2. I can't see how to get the original transaction date,
3. I also can't get the unmodified merchant values. If the merchant is updated in expenses, its modified in the CSV.

I think I could do this using integration services and the API, but if I need to go down that path, that is quite a bit of coding and work.

I was sure during some previous testing that this was possible, but it doesn't seem to be now.

Kjacobsen

bump

Jason Li

Hi @Kjacobsen - happy to help here!

Generally, using :nosymbol will remove that currency symbol. For example:

{expense:amount:nosymbol}

{expense:amount:originalcurrency:nosymbol}

{expense:purchaseamount:nosymbol}

What formula are you using on your end?

For your second question, Expensify goes by the posted date - the formula to use would be {expense:posted}

For the last question, I'm not sure that it's actually possible. When you update a merchant and export the data, that merchant name will be exported - it's not possible to see a history of what's been edited.

Please let us know if you have any further questions!

Kjacobsen

Hi Jason,

{expense:purchaseamount:nosymbol} still includes the currency symbol. {expense:amount} and {expense:purchaseamount} are the same values, and not the original transaction value in whatever currency it was charged in. It seems the {expense:purchaseamount:nosymbol} syntax doesn't work. Maybe a backend error?

{expense:created} is the same value as {expense:posted}, the date the transaction was posted at the bank and not the original transaction date. We need both posted and original due to how long apart these dates can be. I suspect the card import interface accepts mapping a column as the original transaction date, but doesn't store it. Or perhaps when the import happens, the Amount column filles both the created and posted fields for an expense.

Here is a row as defined in the import for the card:

Here are the CSV exportable fields

It seems crazy that so much data is lost between import and export.

Christina Dobryzynski

Thanks for testing those formulas, @Kjacobsen!

I have great news! It turns out the "Original Date" and "Amount with no symbol" are both part of the Detailed Export layout.

You can export a report via the Detailed Export layout by clicking Reports page > select a report to export > click the Export to button > choose Detailed Export from the drop-down list. 

Please feel free to reach out if I can help with anything else!

Kjacobsen

Hi,

That is a PDF report, not CSV correct?

Kjacobsen

My mistake, that is a CSV file and might work for what I need. Is there any plans to expose some of these fields to the custom export?

Kjacobsen

Hi,

Just checking, the Detailed Report, does that have the transaction date? I can just see the posted date.

Kjacobsen

Hi,

The posted date is listed as not available when I do a detailed export.

How can I be sure the Posted/Original date is even being imported, let alone is being exported?

Conor Pendergrast

@Kjacobsen "Transaction Date" is the first column, "Date"!

If you open any company card expense (via the Expenses page), it will show the expense's date, and also show the posted date if that's available for us to import.

Kjacobsen

Seems to only show the posted date in that column.

Cheryl Walsh

@Kjacobsen thanks for reaching out! I see you're chatting to Concierge about this issue- let's get this cleared up for you!