Foreign currency translation error?

poswald
New Contributor II

Source:   Migrated from Champions

I asked this question from OneStream staff at the Splash conference and OneStream support. I thought perhaps someone in this group has run into the same issue:

The standard that appears to be used for calculating FX does not appear to be correct. It appears from the snippet that FX is calculating taking the month to date amount X month end closing rate - month to date amount X month average rate. From the Flow FX (Calculated) snippet:

If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) Then

Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId
Dim rateTypeRev As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp()
Dim rateTypeClo As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability()
Dim closingRate As Decimal =  api.FxRates.GetCalculatedFxRate(rateTypeClo,timeId)
Dim averageRate As Decimal =  api.FxRates.GetCalculatedFxRate(rateTypeRev,timeId)

'Calculate FX activity for current movement between Average and Closing Rate       
api.data.calculate("F#FX_CurMvmt= (F#Activity:C#Local:V#PER * " &  api.Data.DecimalToText(closingRate)  & ")  - (F#Activity:C#Local:V#PER * " &  api.Data.DecimalToText(averageRate)  & ")")

End If

I think this is wrong because we have an account that does not change other than amortization expense. The only change in the account on the cash flow should be amortization expense and FX, but what we have is amortization expense, FX and other.

It seems to me that the formula should be: Year to date amount X month end closing rate - Year to date amount X YTD average rate.

How do you calculate a YTD average rate in OneStream?

If there is a way to calculate a YTD average rate then I think this could be fixed.

3 REPLIES 3

MikeG
Contributor III

The Snippet is doing only what it is says it is doing in the comment above the api.data.calculate(). 'Calculate FX activity for current movement between Average and Closing Rate. It’s assigning that value to a member called F#FX_CurMvmt. What you are describing may be better applied for your use case by doing an FX Override, or using an Account filter in the api.data.calculate(), for that Account or group of Accounts separately.

MarkMatson
New Contributor III

Not sure if this is what you want, and it's clunky, but you can also cycle through the YTD months with this;

For Each timeMember In api.Members.GetMembersUsingFilter(dimpk.GetTimeDimPk, "T#" + api.Pov.Time.name + ".AllPriorInYearInclusive")
' Perform math here using  timeMember.Member.memberId
Next

Cheers

poswald
New Contributor II

I figured out how to do this correctly.  Note: our fiscal year runs from Nov to Oct.  November is the 1st month of our fiscal year.  I calculated each of these in a separate flow member:

YTD balance X Closing rate  (FX_Activity_Closing_rate)

- Periodic balance X average rate  (Cur_Activity_Avg_rate)

- YearSumToDatePrior(Periodic balance X average rate) (FX_Pr_Activity_Avg_rate)

 

If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) Then

Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId
Dim rateTypeRev As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp()
Dim rateTypeClo As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability()
Dim closingRate As Decimal = api.FxRates.GetCalculatedFxRate(rateTypeClo,timeId)
Dim averageRate As Decimal = api.FxRates.GetCalculatedFxRate(rateTypeRev,timeId)

'Calculate Balance sheet activity at the Average rate for each month
api.data.calculate("F#FX_Activity_Closing_rate= ( (F#Activity_Calc:C#Local * " & api.Data.DecimalToText(closingRate) & "))", "A#BalSheet.Base")

End If

 

If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) Then

Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId
Dim rateTypeRev As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp()
Dim rateTypeClo As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability()
Dim closingRate As Decimal = api.FxRates.GetCalculatedFxRate(rateTypeClo,timeId)
Dim averageRate As Decimal = api.FxRates.GetCalculatedFxRate(rateTypeRev,timeId)

'Calculate Balance sheet activity at the Average rate for each month
api.data.calculate("F#FX_Cur_Activity_Avg_rate= ( (F#Activity_Calc:C#Local:V#Periodic * " & api.Data.DecimalToText(averageRate) & "))", "A#BalSheet.Base")

End If

 

Dim curTime As String = api.Pov.Time.Name 'Name of time period being processed
Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId 'Member ID of time period being processed
Dim curMonth As Integer = api.Time.GetPeriodNumFromId(api.Pov.Time.MemberId)
Dim RateTypeClo As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability() 'Rate type of Balance Sheet accounts
Dim Closingrate As Decimal = api.FxRates.GetCalculatedFxRate(RateTypeClo,timeId) 'Closing Rate


'Do not run in November
If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) Then
If (Not curMonth = 1) Then

api.Data.Calculate("F#FX_Pr_Activity_Avg_rate:V#Periodic = F#FX_Cur_Activity_Avg_rate:V#YearSumToDatePrior","A#BalSheet.Base.remove(PLUG_EQIC, 31000, 32000, 37000)")
End If
End If