When exporting expenses from the platform in Excel or CSV format you can customize the columns using formulas.
If you’re applying a formula to any column, the following are a few general rules to keep in mind:
Each formula will be in the following format.
formula_name(parameter_1, parameter 2, … parameter n)
Here, the formula_name is the name of the formula and contains one or more parameters enclosed within brackets.
Parameters can be constant values—e.g., 97, “apple—or be values of other columns.
If a parameter is referring to another column, the formula field() has to be used.
For example: If you want to use the field amount in a formula called sum(), it has to be written as sum(97, field(amount)).
Only the default columns shown in the Column source dropdown can be used in a formula, i.e., user-defined columns cannot be used.
The formulas and parameters are not case sensitive—I.e., sum(97, field(amount)), Sum(97, Field(Amount)) and SUM(97, FieLD(Total)) are the same.
Note: If the field name has spaces, maintain the same inside the formula. For example, if you’re using the column Created on in a formula, space has to be maintained, I.e., Concat(field(created on), “by John”).
If you are using a column that contains special characters in its name, enclose the name within quotes when using it in the formula.
For example: field(“Name(# of expenses)”) where Name(# of expenses) is the name of the column.
List of formulas
1. Field( )
Description: Used for referring to a column when it is being used in another formula
Number of parameters: 1
Parameter: Name of the column
Return type: Value of the referenced column
Example:
sum(97, field(amount)) returns the sum of 97 and the value in the Amount column.
2. Concat( )
Description: Used for concatenating two strings or numbers into a single string
Number of parameters: 2
Parameter types: Both the parameters can be strings, numbers, or references to default columns
Return type: String
Example:
concat(76, 93) returns "7693".
concat("Total amount is", field(amount)) returns "Total amount is 538", given that the value of the amount for that row is 538.
3. Round( )
Description: Used for rounding off a number to its nearest integer
Number of parameters: 1
Parameter type: round(number)
Return type: Number
Example:
round(1.0) returns 1
round(1.23) returns 1
round(1.78) returns 2
round(2) returns 2
round(field(amount)) returns the value of amount rounded off to its nearest integer, given that, amount is of the type Number.
4. Sum( )
Description: Used for adding two numbers
Number of parameters: 2
Parameter type: sum(number, number)
Return type: Number
Example:
sum(9, 10) returns 19
sum(9.1, 10.9) returns 20
sum(10, field(amount)) returns the sum of 10 and the value of amount, given that, amount is of the type Number.
sum(field(total), field(amount)) returns the sum of columns total and amount, given that both of them are of the type Number.
5. Subtract( )
Description: Used for subtracting the right number from the left
Number of parameters: 2
Parameter types: subtract(number, number)
Return type: Number
Example:
subtract(19, 10) returns 9
subtract(19.1, 10.9) returns 8.2
subtract(100, field(amount)) returns (100 - amount), given that, amount is of the type Number.
subtract(field(total), field(amount)) returns (total - amount), given that both of them are of the type Number.
6. Multiply( )
Description: Used for multiplying two numbers
Number of parameters: 2
Parameter types: multiply(number, number)
Return type: Number
Example:
multiply(19, 10) returns 190
multiply(19.1, 10.9) returns 208.19
multiply(100, field(amount)) returns the product of 100 and the value in amount column, given that, amount is of the type Number.
multiply(field(total), field(amount)) returns the product of total and amount, given that both of them are of the type Number.
7. Divide( )
Description: Used for dividing the left number by the right
Number of parameters: 2
Parameter types: divide(number, number)
Return type: Numbers
Example:
divide(290, 10) returns 29
divide(19.1, 10.9) returns 1.752
divide(100, field(amount)) returns (100 / amount), given that, amount is of the type Number.
divide(field(total), field(amount)) returns (total / amount), given that both of them are of the type Number.
8. Abs( )
Description: Used for returning the absolute value of a number.
Number of parameters: 1
Parameter type: abs(number)
Return type: Number
Example:
abs(-90) returns 90
abs(-9.1) returns 9.1
abs(field(amount)) returns the absolute value of amount, given that the amount column is of the type Number.
9. Avg( )
Description: Used for returning the average of two numbers
Number of parameters: 2
Parameter types: avg(number, number)
Return type: Number
Example:
avg(10, 26) returns 13
avg(-10, 26) returns 8
avg(10, field(amount)) returns the average of 10 and amount, given that the amount column is of the type Number.
10. Max( )
Description: Used for returning the maximum of two numbers
Number of parameters: 2
Parameter types: max(number, number)
Return type: Number
Example:
max(10, 16) returns 16
max(10, field(amount)) returns the maximum value among 10 and amount, given that the amount column is of the type Number.
10. Min( )
Description: Used for returning the minimum of two numbers
Number of parameters: 2
Parameter types: min(number, number)
Return type: Number
Example:
max(10, 16) returns 10
max(10, field(amount)) returns the minimum value among 10 and amount, given that the amount column is of the type Number.
11. Substring( )
Description: Used for returning a part of a string
Number of parameters: Minimum-2, Maximum-3
Parameters: substring(string, starting_index [, no_of_characters])
In a given string, extract x number of characters specified by no_of_characters, beginning at the index specified by starting_index. (The first index of the string starts at 0.)
The 3rd parameter is optional. If it's not given, it returns the string starting at the starting_index till the end end of the string,
Parameter types: substring(string, number, number)
Return type: String
Example:
substring(helloworld, 4, 3) returns "owo"
substring(helloworld, 3) returns "loworld"
12. Replace( )
Description: Used for replacing a part of the string with another string
Number of parameters: 3
Parameters: replace(string, target_string, new_string)
In a given string, replace target_string with new_string
Parameter types: replace(string, string, string)
Return type: String
Example:
replace(helloworld, world, universe) returns "hellouniverse"
replace(helloworld, world, field(currency)) returns "helloUSD", given that currency is of type string and has value the USD.
13. Split()
Description: Used for splitting a string based on a delimiter and returning an array containing the split values
Number of parameters: 2
Parameters: split(string, delimiter)
Split the given string using the delimiter specified and return an array containing the splits.
Parameter types: split(string, string)
Return type: an array of strings
Example:
split(hello-world, - ) returns [hello, world]
14. Upper( )
Description: Used for converting a string to its upper case version
Number of parameters: 1
Parameter type: upper(string)
Return type: String
Example:
upper(helloworld) returns HELLOWORLD
14. Lower( )
Description: Used for converting a string to its lower case version
Number of parameters: 1
Parameter type: lower(string)
Return type: String
Example:
lower(HELLOWORLD) returns helloworld
15. Trim( )
Description: Used for removing extra whitespace at the start and end of the string.
Number of parameters: 1
Parameter type: trim(string)
Return type: String
Example:
trim(" helloworld ") returns "helloworld"
16. Power( )
Description: Used for finding the power of a number
Number of parameters: 2
Parameters: power(base, exponent)
Returns the power of the base raised to the exponent.
Parameter types: power(number, number)
Return type: number
Example:
power(2, 3) returns 8
power(field(amount), 2) returns the value of amount raised to 2, given that amount is of the type number.
17. Sqroot( )
Description: Used for calculating the square root of a number
Number of parameters: 1
Parameter type: sqroot(number)
Return type: Number
Example:
sqroot(9) returns 3
sqroot(16) returns 4
18. IsString( )
Description: Used for checking if the parameter is a string or not
Number of parameters: 1
Parameter types: isString(Any)
Return type: Boolean (True or False)
Example:
isString(43) returns False
isString("Helloworld") returns True
isString(field(amount)) returns False if amount is not of the type String.
19. Range( )
Description: Used for getting a range of numbers as a list
Number of parameters: Minimum-1, Maximum-2
Parameter types: range(number [, number] )
Return type: List of numbers
Example:
range(3) returns [1, 2, 3]
range(4, 10) returns 4, 5, 6, 7, 8, 9, 10
20. Length( )
Description: Used for finding the length of an array, string, or an object
Number of parameters: 1
Parameter types: length(array/string/object)
Return type: Number
Example:
length("helloworld") returns 10
length( [1, 5, 8, 10] ) returns 4
21. FormatTime( )
Description: Used for converting a DateTime to a specified format
Number of parameters: Minimum-3, Maximum-5
Parameters: FormatTime(Datetime, input_format, output_format , [fallback, timezone] )
Changes the Datetime from the input_format to the output_format.
fallback and timezone are optional parameters.
fallback is the default value returned if Datetime is null.
timezone is given if the output time is required for a specific timezone. By default it is UTC.
Parameter types: FormatTime(string, string, string, string, string)
Return type: string
Example:
FormatTime('2018-09-01', 'YYYY-MM-DD', 'DD-MMM-YYYY') returns 01-Sep-2018
22. Coalesce( )
Description: Used for returning the first non-null parameter
Number of parameters: Minimum-2, Maximum-10
Parameter types: Each parameter can be of any type
Return type: Any
Example:
coalesce( Null, { }, [ ], 'george', 'lloyd') returns 'george'
All the preferences and customizations you choose in the Advanced settings during export are automatically saved. Once you make an export, the next time you sign in to your account and initiate an export your previous settings will automatically be selected.
This functionality is limited to the same browser. Your settings will be saved as long as you sign in from the same browser and do not clear your local storage.
If you need further assistance with advanced export formulas, reach out to our support team at support@capitalone-fylehq.com.