Deep Dive: Expense level formula

Stevie LaFortuneStevie LaFortune Expensify Team Posts: 121 Expensify Team
edited February 26 in Deep Dives
When creating custom exports, you'll need to decide which details of the expense are important for you to capture.  

Use the formulas listed here to help create an export you can depend on!


1.  Merchant - merchant of the expense
  • {expense:merchant} would output Sharons Coffee Shop and Grill assuming the expense is from Sharons Coffee Shop.
2.  Date - related to the date of the expense in Expensify. This is the date the expense currently shows as having been incurred
  • {expense:created:yyyy-MM-dd} would output 2019-11-05 assuming the expense was created on November 5th, 2019.
  • You can modify the default formatting by removing, adding or reordering the date and time. Please note, dates are case sensitive! That is, mm is different than MM. For a full breakdown, check out the Date Formatting guide here.
3 and 4.  Tax - The tax type and amount applied to the expense line item
  • {expense:tax:field} would output VAT assuming this is the name of the tax field.
  • {expense:tax:ratename} would output the name of the tax rate that was used (ex: Standard). This will show custom if the chosen tax amount is manually entered and not chosen from the list of given options.
  • {expense:tax:amount} would output $2.00 assuming that is the amount of the tax on the expense.
  • {expense:tax:percentage} would output 20% assuming this is the amount of tax that was applied to the subtotal.
  • {expense:tax:net} would output $18.66 assuming this is the amount of the expense before tax was applied.

  • {expense:tax:code} would output the tax code that was set in the policy settings.
5 and 6.  Total - related to the currency type and amount of the expense
  • {expense:amount} would output $3.95 assuming the expense was for three dollars and ninety-five cents
  • {expense:amount:isk} would output Íkr3.95 assuming the expense was for 3.95 Icelandic króna.
  • {expense:amount:nosymbol} would output 3.95. Notice that there is no currency symbol in front of the expense amount because we designated none.

Add an optional extra input that is either a three-letter currency code or nosymbol to denote the output's currency. The default if one isn't provided is USD.
To get the expense in its original currency you can use the following formula
  • {expense:amount:originalcurrency} This gives the amount of the expense in the currency in which it occurred.
  • {expense:amount:originalcurrency:nosymbol} will export the expense in its original currency without the currency symbol.
Original Amount when import with a connected bank  
  • {expense:originalamount} is the amount of the expense imported from your bank or credit card feed. It would output $3.95 assuming the expense equated to $3.95 and you use US-based bank. You may add an optional extra input that is either a three-letter currency code or NONE to denote the output's currency.
  • The corresponding currency code for {expense:originalamount} is simply {expense:currency}.

For expenses imported via CDF/VCF feed only

  • {expense:purchaseamount} is the amount of the original purchase in the currency it was purchased in. Control plan users only.
  • {expense:purchaseamount} would output Irk 3.95 assuming the expense was for 3.95 Icelandic króna, no matter what currency your bank has translated it to.
Other common expense total formulas
  • {expense:amount:negsign} displays negative expenses with a minus sign in front rather wrapped in parenthesis. It would output -$3.95 assuming the expense was already a negative expense for three dollars and ninety-five cents. This formula does not convert a positive expense to a negative value.
  • {expense:amount:unformatted} displays expense amounts without commas. This removes commas from expenses that have an amount of more than 1000. It would output $10000 assuming the expense was for ten thousand dollars.
  • {expense:debitamount} displays the amount of the expense if the expense is positive. Nothing will be displayed in this column if the expense is negative. It would output $3.95 assuming the expense was for three dollars and ninety-five cents.
  • {expense:creditamount} displays the amount of the expense if the expense is negative. Nothing will be displayed in this column if the expense is positive. It would output -$3.95 assuming the expense was for negative three dollars and ninety-five cents.
7.  Total - the category of the expense
  • {expense:category} would output Employee Moral assuming that is the expenses' category.
  • {expense:category:payrollcode}  outputs the payroll code information entered for the category that is applied to the expense. If the payroll code for the Mileage category was 39847, this would output simply 39847.
8.  Attendees - persons listed as attendees on the expense
  • {expense:attendees} would output the name or email address entered in the Attendee field within the expense (ex. [email protected]). 
  • {expense:attendees:count} would output the number of attendees that were added to the expense (ex. 2).8.  Attendees - persons listed as attendees on the expense.

9.  Tags - Tags of the expense - in this example the name of the tag is "Department"

  • {expense:tag} would output Henry at Example Co. assuming that is the expenses' tag. 
Multiple Tags - Tags for companies that have multiple tags setup.
  • {expense:tag:ntag-1} outputs the first tag the user chooses.
  • {expense:tag:ntag-3} outputs the third tag the user chooses.

10.  Description - The description on the expense

  • {expense:comment} would output "office lunch" assuming that is the expenses' description.

11.  Report title - the title of the report the expense is part of

  • {report:title} would output "Expense Expenses to 2019-11-05 assuming that is the report's title.

12.  Receipt - all things related to the receipt - MMC, type, URL

  • {expense:receipt:type} would output eReceipt if the receipt is an Expensify Guaranteed eReceipt.
  • {expense:receipt:url} would output a link to the receipt image page that anyone with access to the receipt in Expensify could view.
  • {expense:receipt:url:direct} would show the direct receipt image url for download. 
  • {expense:mcc} would output 3351 assuming that is the expenses' MCC (Merchant Category Code of the expense).
*Note, we only have the MCC for expenses that are automatically imported or imported from an OFX/QFX file. For those we don't have an MCC for the output would be (an empty string).

13.  Card name/number expense type

  • {expense:card} Manual/Cash Expenses — would output Cash assuming the expense was manually entered using either the website or the mobile app.
  • {expense:card} Bank Card Expenses — would output [email protected] – 1234 assuming the expense was imported from a credit card feed.
Note - If you do not have access to the card that the expense was created on 'Unknown' will be displayed.  If cards are assigned to users under Domain, then you'll need to be a Domain Admin to export the card number.

Related articles
Have a question or want to know more? Start a discussion here!
Tagged:
Sign In or Register to comment.