We help IT Professionals succeed at work.

ColdFusion SpreadsheetSetCellValue for Excel's formula RATE not working in all cases

Errol Farro
Errol Farro asked
on
Medium Priority
165 Views
Last Modified: 2019-09-17
Excel's formula RATE is used to get the interest rate per period of an annuity

=RATE (nper, pmt, pv, [fv], [type], [guess])
nper - The total number of payment periods.
pmt - The payment made each period.fv - [optional] The future value, or desired cash balance after last payment. Default is 0.
type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.
guess - [optional] Your guess on the rate. Default is 10%.
pv - The present value, or total value of all loan payments now.


I am trying to mimic the RATE formula in ColdFusion and have been successful until a certain point (see example below).

For nPER > 43 my Coldfusion function returns the wrong result.

Excel result : =RATE(44,223.51,-5000) =  0.03476
Coldfusion result : 0.0135061661

Any help is greatly appreciated

<cfloop index="nIdx" from="1" to="99">
	
		<cfinvoke   method="getAPR2" returnvariable="nAPR">
		    <cfinvokeargument name="parmLoan"  value="5000">
		    <cfinvokeargument name="parmDuration"  value="#nIdx#">
		    <cfinvokeargument name="parmMonthlyPayment"  value="223.51">
		</cfinvoke>

		<cfdump var = "#nIdx#"> .... <cfdump var = "#nApr#"><br>

</cfloop>

<!---------------------------------------------------------------
    getAPR2 - 2019-09-01 using Central Bank's document
----------------------------------------------------------------->
<cffunction name="getAPR2" returntype ="numeric">
    <cfargument name="parmLoan" required="true" type="numeric">
    <cfargument name="parmDuration" required="true" type="numeric">	<!--- In months --->
    <cfargument name="parmMonthlyPayment" required="true" type="numeric">

	<cfscript> 
		theSheet=SpreadsheetNew(); 
	</cfscript> 

    <cfset var MYVAR = structNew()>
	<cfset SpreadsheetSetCellValue(theSheet,ARGUMENTS.parmLoan,20,1)> 
	<cfset SpreadsheetSetCellValue(theSheet,ARGUMENTS.parmDuration,21,1)> 
	<cfset SpreadsheetSetCellValue(theSheet,ARGUMENTS.parmMonthlyPayment,22,1)>
	<cfset SpreadsheetSetCellFormula(theSheet,"RATE(a21,a22,-a20)",23,1)> 
	

	<cfset MYVAR.nAPR = spreadsheetGetCellValue(theSheet, 23,1)>
	
    <cfreturn MYVAR.nAPR>
 </cffunction>

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
I don't have an answer, other than to say you may be running into some RATE() function bugs in POI.  As it doesn't look like it's been fixed, you'd need to roll your own or find an alternative.  Unfortunately I don't know RATE() well enough to suggest one.

Author

Commented:
Since the bug has not been fixed, I have been trying the below as an alternative
1.      Generate Excel file
2.      Use VB script to convert Excel into CSV
3.      Read CSV file

Unfortunately I have not been successful yet as CFEXECUTE to run the VB script does not work.

Your assistance is highly apreciated

Generate the Excel file
<cfscript>
	theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "newSpreadsheet.xls";
	theSheet = SpreadsheetNew("Expenses");
	SpreadsheetSetCellValue(theSheet,"1250",1,1);
	SpreadsheetSetCellValue(theSheet,"90",2,1);
	SpreadsheetSetCellValue(theSheet,"50000",3,1);
	SpreadsheetSetCellFormula(theSheet,"(1+RATE(A2,A1,-A3))^12-1",4,1);
</cfscript>
 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet" overwrite=true>

Open in new window


B9.bat – runs as expected from command line
cvtExcelToCSvPROMPT C:\ColdFusion11\cfusion\wwwroot\Test\newSpreadsheet.xls C:\ColdFusion11\cfusion\wwwroot\Test\newSpreadsheet.csv

Open in new window


cvtExcelToCSvPROMPT.vbs
if WScript.Arguments.Count < 2 Then
    WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit

Open in new window


Convert XLS to CSV .CFM
<cfexecute variable="gm"
	errorVariable="errorOut"
	name="c:\windows\system32\cmd.exe"
	timeout="30"
	arguments="/c  C:\ColdFusion11\cfusion\wwwroot\Test\b9.bat" />

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
With a small change to the VBS script, this worked under CF2016

cvtExcelToCSvPROMPT.vbs  (suppress alerts)

...
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = false
...

Open in new window


Run with cscript.exe

//NoLogo - Specifies that the Windows Script Host banner is not displayed before the script runs.
/b - Specifies batch mode, which does not display alerts, scripting errors, or input prompts.

<cfexecute name = "C:\Windows\System32\CScript.exe"
       arguments = "//NoLogo /b c:\path\cvtExcelToCSvPROMPT.vbs C:\path\test.xls C:\path\spreadsheet.csv"
       variable = "savedOutput"
       timeout = "3600"
       errorVariable="err">
</cfexecute>

Open in new window

Author

Commented:
Tried the solution on Coldfusion11 and Coldfusion2018 without luck.

Can you think of some other alternatives on how I can get the desired result using Excel formula ?

I ran out of options
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
Edit: Although server side automation isn't generally recommended, the only things I can think of require Excel, which isn't currently working for you.

I re-checked and it worked under CF2018 too. So there must be something different in our environments.  cfexecute woes are usually due to permissions problems.  My environment

- Excel 32 bit installed on server machine
- CF service is running under Local System account
- Allow service to interact with Desktop is UN-checked.
- Win 7 64 bit

I found a few articles which mention issues with Excel:

Even though the script or program is executing under a user account, when Excel is launched through COM automation it is executed under the Local System user identity. This identity does not have a user profile, and this causes Excel to fail because it is designed to assume that it is running in an interactive user session.

The workaround it to create the missing "Desktop" folder

If the script is written using VBScript, JScript, or another Windows Scripting Host language, create the folder "C:\Windows\System32\config\systemprofile\Desktop". Do this regardless of whether the operating system is 32-bit or 64-bit, because these scripts always run as 32-bit scripts.

The Desktop folder does exist on the test machine (64 bit).  When I deleted that folder, the code stopped working.  So that may be why it works for me. Check if the folder exists in your environment, and if not create it.

On 32-bit platforms, create the folder "C:\Windows\System32\config\systemprofile\Desktop".
On 64-bit platforms, create the folder "C:\Windows\SysWOW64\config\systemprofile\Desktop".

Author

Commented:
Hello _agx_

I trid all you suggested to no avail. CFEXECUTE indeed has it woes. Thank you for the time invested in assisting me
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Sorry, you weren't able to get it working.  Figuring out the correct permissions is sometimes an extended process ....

When you successfully ran it from the command line, you were probably logged in under your own windows account, which has greater permissions.  The CF service typically runs under the "Local System".  As a test,  temporarily change the "Log on" account used by the CF service

          Services > (ColdFusion Service Name) > Log On Tab > (Enter the windows credentials used for the CLI test)
          Check "Allow service to interact with desktop"

Restart the CF Service, and run the test code again. If it succeeds, that'll at least confirm it's a permissions problem.

When finished, don't forget to reset the CF Service back to the original Log On account, and uncheck "Allow service to interact with desktop"

Author

Commented:
I changed the services to windows credential. Now I am getting the below message. As you said, it is a permission issue


The following information is meant for the website developer for debugging purposes.
Error Occurred While Processing Request
Timeout period expired without completion of C:/Windows/System32/CScript.exe
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Check the Task Manager. Is there an instance of cscript.exe running?

Author

Commented:
N0, there is no instance of csscript.exe running
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Which o/s? Also, is CF running in a sandbox or any anti-virus software running that might interfere?

Try a simple helloworld example with cscript. Does it still timeout?

helloWorld.vbs
WScript.Echo "Hello world!"

Open in new window

CF
<cfexecute name = "C:\Windows\System32\CScript.exe"
       arguments = "//NoLogo C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\helloWorld.vbs" 
       variable = "savedOutput"
       timeout = "30"
       errorVariable="err">
</cfexecute>
<cfoutput>
  <!--- not sure if CF11 supports isNull, so using alternative --->
  <cfif structKeyExists(variables, "savedOutput")>
	savedOutput = #savedOutput#
  </cfif>
  <cfif structKeyExists(variables, "err")>
	err = #err#
  </cfif>
</cfoutput>

Open in new window

Author

Commented:
This is the output I got ---> savedOutput = Hello world! err =

O/S = WIndows 10
Only anti-virus is windows defender
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Two other things you could try for grins.  FWIW, I didn't need to do either, but .. I suspect we're not using the same o/s. Since the test is quick, it's worth trying.

1) A few threads mentioned they created the "Desktop" folder in both the 32bit/64 bit locations. Try that and re-run the test code.

            "C:\Windows\System32\config\systemprofile\Desktop"
             "C:\Windows\SysWOW64\config\systemprofile\Desktop"

2) Again, temporarily using the windows credentials that ran cscript.exe, enable the "Allow service to interact with desktop" setting. Restart CF and re-run the test code.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Sorry, didn't see your reply ...

>> This is the output I got ---> savedOutput = Hello world! err =

Okay, then the problem isn't cscript.exe. What happens if you change the HelloWorld.vbs file to this?

WScript.Echo "Preparing to open Excel..."

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.Quit

WScript.Echo "Finished. Closed Excel"

Open in new window

Author

Commented:
Got the same result

savedOutput = Hello world! err =

Author

Commented:
This is the new result ---> time out !!!! See below


The following information is meant for the website developer for debugging purposes.
Error Occurred While Processing Request
Timeout period expired without completion of C:/Windows/System32/CScript.exe
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Ahh, good. So we know the problem is opening Excel.  What version of Excel and bitness?

Also, is there another CF machine you can test this from, to rule out Win10 as the culprit?

Edit: Are there any errors in the CF logs or Windows Event logs?

Author

Commented:
Message in EXCEPTION.log
=================================
"Error","http-bio-8500-exec-5","09/06/19","14:23:43",,"Timeout period expired without completion of C:\Windows\System32\CScript.exe The specific sequence of files included or processed is: C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\helloWorld.cfm, line: 5 "
coldfusion.tagext.lang.ExecuteTag$TimeoutException: Timeout period expired without completion of C:\Windows\System32\CScript.exe
      at coldfusion.tagext.lang.ExecuteTag.doStartTag(ExecuteTag.java:177)
      at cfhelloWorld2ecfm1080557222.runPage(C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\helloWorld.cfm:5)


Message in coldfusion-out.log
=============================
Sep 6, 2019 14:23:43 PM Error [http-bio-8500-exec-5] - Timeout period expired without completion of C:\Windows\System32\CScript.exe The specific sequence of files included or processed is: C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\helloWorld.cfm, line: 5


MEssage in application.log
==========================
"Error","http-bio-8500-exec-5","09/06/19","14:23:43",,"Timeout period expired without completion of C:\Windows\System32\CScript.exe The specific sequence of files included or processed is: C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\helloWorld.cfm, line: 5 "

 
Result on another server: CF2018 environment, Windows server 2016, no Office365 but LibreOffice365
==================================================================================================
savedOutput = Preparing to open Excel... Finished. Closed Excel err =

Note
====
On my PC, running Windows10, I am using Office365
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
> CF2018 environment, Windows server 2016 no Office365 but LibreOffice365

Interesting, I'm surprised it works with Libre.  Was the test using "Local System" or your windows account?  

If you try it with the original script, does it work (or is that the machine you said failed with CF2018)?

Author

Commented:
On the server, only the XLS file is being created.

Maybe I am overlooking someithing. Can you take a peek please


Directyory on server
=====================
C:\ColdFusion2018\cfusion\wwwroot\vbScriptTest


.cfm
======
<cfscript>
      theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "newSpreadsheet.xls";
      theSheet = SpreadsheetNew("Expenses");
      SpreadsheetSetCellValue(theSheet,"1250",1,1);
      SpreadsheetSetCellValue(theSheet,"90",2,1);
      SpreadsheetSetCellValue(theSheet,"50000",3,1);
      SpreadsheetSetCellFormula(theSheet,"(1+RATE(A2,A1,-A3))^12-1",4,1);
</cfscript>
 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet" overwrite=true>

<cfexecute name = "C:\Windows\System32\CScript.exe"
       arguments = "//NoLogo  C:\ColdFusion11\cfusion\wwwroot\Test\cvtExcelToCSvPROMPT.vbs C:\ColdFusion11\cfusion\wwwroot\Test\newSpreadsheet.xls C:\ColdFusion11\cfusion\wwwroot\Test\spreadsheet.csv"
       variable = "savedOutput"
       timeout = "10"
       errorVariable="err">
</cfexecute>



cvtExcelToCSvPROMPT.vbs
=======================
if WScript.Arguments.Count < 2 Then
    WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = false

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:

Directory structure:
C:\ColdFusion2018\cfusion\wwwroot\vbScriptTest

Code:
<cfexecute
 arguments = "//NoLogo  C:\ColdFusion11\cfusion\wwwroot\Test\cvtExcelToCSvPROMPT.vbs C:\ColdFusion11\cfusion\wwwroot
...

Some of the paths are different.  

To keep things simple, I skipped the spreadsheet functions and just used a static xls file.  Everything's kept within this single directory

C:\ColdFusion11\cfusion\wwwroot\vbScriptTest
- cvtExcelToCSvPROMPT.vbs
- test.xls
- test.cfm


Code:
<cfexecute name = "C:\Windows\System32\CScript.exe"
       arguments = "/C //NoLogo /b C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\cvtExcelToCSvPROMPT.vbs C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\test.xls C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\spreadsheet.csv"
       timeout = "30"
       variable = "savedOutput"
       errorVariable="err">
</cfexecute>
<cfoutput>
  <cfif structKeyExists(variables, "savedOutput")>
      savedOutput = #savedOutput#
  </cfif>
  <cfif structKeyExists(variables, "err")>
      err = #err#
  </cfif>
</cfoutput>

Author

Commented:
Guess I have been looking too long at the code - thanks for the correction



Result
======
CSV is not created
savedOutput = err =

.cfm
====

<cfscript>
      theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "test.xls";
      theSheet = SpreadsheetNew("Expenses");
      SpreadsheetSetCellValue(theSheet,"1250",1,1);
      SpreadsheetSetCellValue(theSheet,"90",2,1);
      SpreadsheetSetCellValue(theSheet,"50000",3,1);
      SpreadsheetSetCellFormula(theSheet,"(1+RATE(A2,A1,-A3))^12-1",4,1);
</cfscript>
 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet" overwrite=true>

<cfexecute name = "C:\Windows\System32\CScript.exe"
       arguments = "//NoLogo /b C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\cvtExcelToCSvPROMPT.vbs C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\test.xls C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\spreadsheet.csv"
       timeout = "30"
       variable = "savedOutput"
       errorVariable="err">
</cfexecute>
<cfoutput>
  <cfif structKeyExists(variables, "savedOutput")>
      savedOutput = #savedOutput#
  </cfif>
  <cfif structKeyExists(variables, "err")>
      err = #err#
  </cfif>
</cfoutput>



cvtExcelToCSvPROMPT.vbs
====
if WScript.Arguments.Count < 2 Then
    WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = false

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
>>  C:\ColdFusion11\cfusion\

Oh, I thought this was under CF2018?

Just to be sure we're not getting tripped up by bad paths or something extraneous,  create a folder with only these 3 files:

1. test.cfm
2. test.xls  (use the file you already created)
3. cvtExcelToCSvPROMPT.vbs

test.cfm - uses dynamic paths (and unique CSV file name)

<cfset dir = getDirectoryFromPath(getCurrentTemplatePath())>
<cfset args = "//NoLogo #dir#cvtExcelToCSvPROMPT.vbs #dir#test.xls #dir##createUUID()#.csv">

<cfexecute name = "C:\Windows\System32\CScript.exe"
       arguments = "#args#"
       variable = "savedOutput"
       timeout = "30"
       errorVariable="err">
</cfexecute>

<cfoutput>
  
  <h4>Directory</h4> #dir#
  <h4>args</h4> #args#
  <h4>variable</h4> <pre>#savedOutput#</pre>
  <h4>errorVariable</h4> #err#
</cfoutput>

Open in new window


cvtExcelToCSvPROMPT.vbs - added error code, removed validation and alert suppression

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
WScript.Echo "Err.Number "& Err.Number & Chr(10)
WScript.Echo "Err.Description "& Err.Description & Chr(10)

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit

Open in new window

Author

Commented:
Dear _agx_, sorry for not having answered earlier. Your solution did work. Thank you very much for saving the day. Appreciate it !!!!
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Great! Glad we finally got it working!