If you do business in multiple currencies and need to build finance reports around something other than 'Deal amount', this hack is for you.
In HubSpot, if you do business in multiple currencies, you can add currencies to your account and set an exchange rate compared to your primary company currency to more accurately track the amount of your deals.
Getting started: Default currency Deal properties
The exchange rates inputted here are used in some default deal properties that come pre-built into your HubSpot portal:
- Currency: the currency that the total value of the deal is set in. This property will only appear if you've set up multiple currencies in your account
- Amount in company currency: the total value of the deal in your selected company currency, when your HubSpot account uses more than one currency.
- This value is calculated based on the exchange rates set in your HubSpot account.
- Changes to the exchange rate set will update the value until the deal is in a closed-won or closed-lost stage.
- Once the deal is moved to a closed-won or closed-lost stage, the value will be set and will only update if the historical exchange rate is edited.
Step 1: Create a custom number property
If you want to report in something other than deal amount within your portal, margin for example, the first step will be to create a custom number property for the value of the margin in the deal currency to be inputted into.
N.B. Formatting a number property as a currency will set the currency as that of your account default currency settings so it is not suitable to format this property.
Check this hack for how to populate a custom 'Margin' deal property automatically based on the values held in the associated line items: HubSpot Custom Objects: Use a custom object to set goals and forecasting against margin in your HubSpot portal
Step 2: Create a custom calculation property
Now create a calculation property to calculate the exchange rate currently set in your HubSpot portal - by dividing the value held in the 'Amount in company currency' property by the value held in the 'Amount' property - to be multiplied by your custom currency property.
(Margin in deal currency + 0) x (Amount in company currency / Amount)
Step 3: Create a custom currency property
Next, a custom number property formatted as a currency should be created for the calculated value to be stamped across via workflow. As mentioned above, this will default to the currency of your portal so it is fine to be used in this instance.
Step 4: Build a workflow to stamp the calculation over to your property
The final step is to build a workflow to stamp the value held in the calculation property over to the currency property every time it is updated - because the exchange rate has been updated in your portal or the values held in the custom number property (in this example the 'Margin in deal currency' property).
You'll want to add a filter to the trigger to only enrol active deals, so as to hold the value in accordance with the exchange rate at the point the deal was closed, and be sure to turn on re-enrollment.
Use your new property!
Add your properties to the sidebar of your deal records and deal create forms and train your users on the process - if, like in this example, the margin in deal currency is being auto-populated from the value held in the line items there won't be any action for your users to take, every property will be populated by HubSpot!
And you can use it in your reports too...
Author: Hannah Fisher
HubSpot CRM Platform Consultant