Accounting Seed Financial Cube Formula (ASFC) for Salesforce

Learn how to use ASFC formula in XL-Connector.

In order to support the custom ASFC formula for Accounting Seed orgs connected to Salesforce data, you’ll need to create a custom formula in your VBA editor as follows:

1. Click on Developer -> Script Editor in Excel
2. Create the following functions in VBA editor:

   Public Function ASFC(ledger As String, glAccount As String, glav1 As String, glav2 As String, glav3 As String, glav4 As String, period As String, amt_type As String)
    Dim addin As Office.COMAddIn
    Dim automationObject As Object
    Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
    
    
     '------------------VALIDATIONS----------------------
       
       If IsNull(ledger) Or IsEmpty(ledger) Then
           ASFC = "Ledger is required"
           End
       End If
       If IsNull(glAccount) Or IsEmpty(glAccount) Then
           ASFC = "GL Account is required"
           End
       End If
       If IsNull(glav1) Or IsEmpty(glav1) Then
           glav1 = "NONE"
       End If
       If IsNull(glav2) Or IsEmpty(glav2) Then
           glav2 = "NONE"
       End If
       If IsNull(glav3) Or IsEmpty(glav3) Then
           glav3 = "NONE"
       End If
       If IsNull(glav4) Or IsEmpty(glav4) Then
           glav4 = "NONE"
       End If
       If IsNull(period) Or IsEmpty(period) Then
           ASFC = "Period is required"
           End
       End If
       If IsNull(amt_type) Or IsEmpty(amt_type) Then
           ASFC = "Amount Type is required"
           End
       End If
       
       If Not amt_type = "OPB" And Not amt_type = "BUD" And Not amt_type = "MTD" And Not amt_type = "YTD" Then
           ASFC = "Invalid parameter: 'Amount type'"
           End
       End If
       '------------------END VALIDATIONS----------------------
       If amt_type = "OPB" Then
           q_amt_type = " sum(AcctSeed__Opening_Balance__c) "
       End If
       If amt_type = "BUD" Then
           q_amt_type = " sum(AcctSeed__Amount__c) "
       End If
       If amt_type = "MTD" Then
           q_amt_type = " sum(AcctSeed__Current_Period__c) "
       End If
       If amt_type = "YTD" Then
           q_amt_type = " sum(AcctSeed__Year_To_Date__c) "
       End If
       
       If glav1 = "NONE" Then
           q_glav1 = "and AcctSeed__GL_Account_Variable_1__c = NULL "
       Else
           If glav1 = "ALL" Then
               q_glav1 = " "
           Else
               q_glav1 = "and AcctSeed__GL_Account_Variable_1__r.Name = '" + glav1 + "' "
           End If
       End If
       
       If glav2 = "NONE" Then
           q_glav2 = "and AcctSeed__GL_Account_Variable_2__c = NULL "
       Else
           If glav2 = "ALL" Then
               q_glav2 = " "
           Else
               q_glav2 = "and AcctSeed__GL_Account_Variable_2__r.Name = '" + glav2 + "' "
           End If
       End If
       If glav3 = "NONE" Then
           q_glav3 = "and AcctSeed__GL_Account_Variable_3__c = NULL "
       Else
           If glav3 = "ALL" Then
               q_glav3 = " "
           Else
               q_glav3 = "and AcctSeed__GL_Account_Variable_3__r.Name = '" + glav3 + "' "
           End If
       End If
       If glav4 = "NONE" Then
           q_glav4 = "and AcctSeed__GL_Account_Variable_4__c = NULL "
       Else
           If glav4 = "ALL" Then
               q_glav4 = " "
           Else
               q_glav4 = "and AcctSeed__GL_Account_Variable_4__r.Name = '" + glav4 + "' "
           End If
       End If
       
    
       q_ledger = EscapeQuote(ledger)
       q_glAccount = EscapeQuote(glAccount)
       q_period = EscapeQuote(period)
       query = "SELECT " + q_amt_type + " FROM AcctSeed__Financial_Cube__c WHERE AcctSeed__Ledger__r.Name = '" + q_ledger + _
       "' and AcctSeed__GL_Account__r.Name = '" + q_glAccount + _
       "' and AcctSeed__Accounting_Period__r.Name = '" + q_period + _
       "' " + q_glav1 + q_glav2 + q_glav3 + q_glav4
   
       ar = automationObject.RetrieveData(query, False, False, errorText)
       
       If errorText = "The column(s) you selected in your query is (are) empty in all the records." Then
       ASFC = 0
       ElseIf Not errorText = Empty Then
         ASFC = errorText
       Else
         ASFC = ar(0, 1)
       End If
   
   End Function
   Function EscapeQuote(value As String)
       Test = Replace(value, "'", "/'")
       EscapeQuote = Test
   End Function

Now if you want this macro to appear in all documents that you open on this computer click on File -> Save As and save the document as an Excel Add-In:

With these formulas you can create comprehensive Financial reports: