topic Re: Excel VBA to automate JMP in Discussions

Excel VBA to automate JMP

KL — Wed, 08 Mar 2017 13:42:50 GMT

Hi Folks, i am trying to run JMP time series analysis from excel VBA.

below is the codes

Sub LData()

Dim myjmp As JMP.Application
Dim jmp_doc As JMP.Document
Dim jmp_BL As JMP.DataTable
Dim jmp_dt As JMP.DataTable
Dim jmp_dtFinal As JMP.DataTable
Dim jmpstr As String
Set myjmp = CreateObject("JMP.Application")
myjmp.Visible = True

Set jmp_doc = myjmp.OpenDocument("C:\Data_Doc1.xlsx")
Set jmp_BL = jmp_doc.GetDataTable

Dim TS As JMP.TimeSeries
Set TS = jmp_doc.CreateTimeSeries()

Dim Cont As JMP.timeSeriesConstraintConstants
Winter = tsConstraintZeroToOne

Dim Modd As JMP.timeSeriesModelConstants
Modd = tsModelWinters

TS.LaunchAddYSeries ("Sell")
TS.LaunchAddNumAutoCorrLags (25)
TS.Launch

Call TS.SmoothingModel(Modd, Winters)

End sub

it gives me a error message in JMP

unknown operation
Seasonal model needs numeric argument: periods per season

i did not have the option to key in periods per season when i was doing up Call.TS.SmoothingModel(Modd,Winters)

need your help to enlighten me what i am missing

Thanks a lot in advance.

Cheers.

Kenny

Re: Excel VBA to automate JMP

briancorcoran — Wed, 08 Mar 2017 18:15:02 GMT

Can you produce the analysis that you desire using JMP's Time Series dialogs? If so, can you post the script from a successful analysis here (report Red Triangle->Save Script->To Script Window).

Thanks,

Brian Corcoran

JMP Development

Re: Excel VBA to automate JMP

KL — Thu, 09 Mar 2017 01:33:27 GMT

Time Series(
Y( :Sell ),
Number of Forecast Periods( 12 ),
Winters Method( 48, Zero to One, Confidence Intervals( 0.8 ) ),
SendToReport(
Dispatch(
{"Model Comparison"},
"2",
ScaleBox,
{Add Ref Line( 0, Dashed, "Black" )}
),
Dispatch(
{"Model Comparison"},
"8",
ScaleBox,
{Add Ref Line( 0, Dashed, "Black" )}
),
Dispatch(
{"Model Comparison"},
"3",
ScaleBox,
{Add Ref Line( 70, Solid, "Blue" ), Show Major Grid( 1 )}
),
Dispatch(
{"Model Comparison"},
"4",
ScaleBox,
{Add Ref Line( 0, Dashed, "Black" )}
),
Dispatch(
{"Model Comparison"},
"5",
ScaleBox,
{Add Ref Line( 0, Dashed, "Black" )}
)
)
);

thanks a lot for the help

Re: Excel VBA to automate JMP

briancorcoran — Thu, 09 Mar 2017 13:57:41 GMT

Unfortunately, it looks like the automation method for Smoothing Model is out of date with the options in Time Series. I will contact Tech Support to let them know that the issue should be entered to correct this in a future version of the product. The only workaround that I am aware of for this case is to use the automation "RunCommand" method to submit JSL directly to JMP to do the analysis. So, you could do something like below (using the sample data "Raleigh Temps")

Dim DocX As JMP.Document
DocX = myJMP.OpenDocument("c:\program files\sas\jmppro\14\samples\data\Time Series\Raleigh Temps.jmp")
myJMP.RunCommand("Time Series(X( :Month Number ),Y( :Temperature ),Winters Method( 48, Zero to One, Prediction Interval( 0.8 ) ))")

Re: Excel VBA to automate JMP

KL — Thu, 09 Mar 2017 14:02:37 GMT

no problem, thanks a lot for your help.