Way to use excel formula

  • 1
  • Question
  • Updated 1 year ago
  • Answered
Is there a way to use excel formula in formual fields? 
Like gross annual percentage rate o other finance formula
Thank you
Photo of Andrea

Andrea

  • 460 Points 250 badge 2x thumb

Posted 1 year ago

  • 1
Photo of Stephen Sells

Stephen Sells, Official Rep

  • 16,856 Points 10k badge 2x thumb
Not at this time. The skuid table is being more and more robust but we are not yet at a time where an excel formula could be copied and pasted into a formula field.
Photo of Andrea

Andrea

  • 460 Points 250 badge 2x thumb
A way to calculate in a ui-field something like:

private double calculateRate(double nper, double pmt, double pv, double fv, double type, double guess) {
              //FROM MS http://office.microsoft.com/en-us/excel-help/rate-HP005209232.aspx
              
              integer FINANCIAL_MAX_ITERATIONS = 20;//Bet accuracy with 128
              double FINANCIAL_PRECISION = 0.0000001;//1.0e-8
        
              double y, y0, y1, x0, x1 = 0, f = 0, i = 0;
              double rate = guess;
              if (Math.abs(rate) < FINANCIAL_PRECISION) {
                 y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
              } else {
                 f = Math.exp(nper * Math.log(1 + rate));
                 y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
              }
              y0 = pv + pmt * nper + fv;
              y1 = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
        
              // find root by Newton secant method
              i = x0 = 0.0;
              x1 = rate;
              while ((Math.abs(y0 - y1) > FINANCIAL_PRECISION) && (i < FINANCIAL_MAX_ITERATIONS)) {
                 rate = (y1 * x0 - y0 * x1) / (y1 - y0);
                 x0 = x1;
                 x1 = rate;
        
                 if (Math.abs(rate) < FINANCIAL_PRECISION) {
                    y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
                 } else {
                    f = Math.exp(nper * Math.log(1 + rate));
                    y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
                 }
        
                 y0 = y1;
                 y1 = y;
                 ++i;
              }
              return rate;
           }
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
Andrea,

You can setup a UI only field to show the value, but you'll need to calculate the value in a JavaScript snippet.

Is this calculation done on values in 1 row of an object?  You may want to put this logic into a trigger.  It will be much faster to load this value from Salesforce rather than calculate it each time it is displayed.

Thanks,

Bill
Photo of Andrea

Andrea

  • 460 Points 250 badge 2x thumb
Hi Bill,
thanks a lot for your help, 
right now the process is executed in a UI-only model, and this is giving me some problems, due to the fact that I can't order the results using a particular uionly field. 
Do you think that I can resolve the problem creating an sf object linked to the User, so that I can temporarily store in it the values? If the answer is yes, do you now any way to copy rows from the uionly model to the model based on new sf object? I tried with "adopt rows in a model" but for this action the two models need to be based on the same object, and it's not my case.

Thank again, andrea
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
Andrea,

When you sort a column in Skuid, it does a new SOQL pull from Salesforce sorted by whatever fields you selected for sorting (i.e. Salesforce handles the sorting).  I still think your best option is to add a field to your object that holds the values you use for your calculation.  Then add a trigger to update this field in Salesforce.  Then your Skuid page is simple.

Since you have the UI only model working, I'd say that you should add a sort function to your calculation or a separate snippet that you can run by pressing a button to sort the results.

I am not sure about your use case.  Is your usage just to provide a way to calculate gross annual percentage but not 'save' the numbers?

Thanks,

Bill