Specific question on batch macro

[kajals]

I created a workflow that does some interpolation & extrapolation calculations to get expected Present Value of Ending Surplus (PVES) based on a certain interest/treasury rate. See InterestRateSensitivity_v6.yxmd.

What I want to do now is vary the input: treasury rate (variable ExpIR_nonVA) multiple times and get the expected PVES for each iteration. I tried to use a batch macro for the same but I'm getting some errors. Honestly, I'm not even sure whether the steps I included in the macro would get the desired results. See Graphs.xlsx for the desired graphs (cell C40 onwards are the treasury rates and following columns will be the expected PVES output for each company & scenario). Graphs is not an issue, just getting those columns for expected PVES for each treasury rate. See InterestRateSensitivity_v6.yxmc (modification is highlighted in a tool container under 'Calculations' text box) and Expected PVES Calcs.yxmc (batch macro).

Any help would be appreciated!!

[KylieF]

Hello @kajals ,  sounds like you're running into some issues with developing a macro. This might be something that maybe our ACEs could assist you with. @BenMoss , @CharlieS , @cplewis90   Do you have any recommendations on best next steps for developing this batch macro? Thanks! KylieF

[cplewis90]

Hey @kajals,

I am in training all day today, but I will check this out and try and get something over to you tomorrow.

[kajals]

Thanks. 

Please ignore the second para of my question. All I need to do is change row 3 column 2 (Treasury Rate) of the input tool (named A) multiple times (you may use any dummy numbers) and give different outputs (named B) for the same. 

Please see workflow below:

[CharlieS]

Hi @kajals 

Sorry a response has taken so long, my Community time isn't always consistent. Thanks for sharing those files, but we are unable to run the workflow (InterestRateSensitivity_v6.yxmd) with a missing dependency. There appears to be an Excel file (Alteryx PVES for Sensitivities_CFT 2020_v4.xlsx) that was not attached, which means we are unable to run your workflow. If this is sensitive data, maybe there's a sample or fabricated version that could be shared?

For the time being, I think there's still some work we can do. Looking at the macro you provided (Expected PVES Calcs.yxmc), there's a couple steps you can take to turn this into a batch macro that updates the [ExpIR_nonVA] value. Add the Control Parameter tool, which I updated the label to the variable we will be using, and then add a Formula tool which will update the value of the [ExpIR_nonVA] field each batch. 

To use this macro in the workflow, you'd need to create a list of [ExpIR_nonVA] values you'd like the macro to run, and input that list into the control parameter input on the macro. It looks like you're already preparing the [ExpIR_nonVA] value and appending it in your workflow, so it's a similar concept to input the value(s) as a control parameter the same way you append earlier. 

If you are able to provide an input for the Excel file above, I'm sure we could prepare a running workflow. It should be noted that there will likely be other changes in your workflow after these calculations to prepare an output/graphs which handle the multiple [ExpIR_nonVA] values appropriately. 

[kajals]

Hi @CharlieS,

Thank you very much for responding to my question. It looks like the solution you provided should work.  But it'd be great if I could see the output too for different [ExpIR_nonVA] values (see attached). Hence I am attaching a sample fabricated version of the inputs for PVES that you could use (see attached). I am also attaching a cleaner version of the Alteryx yxmd workflow (see attached v6). I have highlighted the output that I need for this batch macro (yellow tool container named 'B'). I only need the output 'By Company' for the varying values of [ExpIR_nonVA].

Thanks again for your help on this. Much appreciated!

[CharlieS · Accepted solution]

Thanks for that! 

I've taken that workflow, added the batch macro, and made some downstream changes so the reporting is grouped by the different values. I've added green comments where I made changes.

Check it out and let me know if you have any questions. 

[kajals]

Hi @CharlieS,

Thank you very much for that workflow, it resolved the issues I had. Sorry for the late response, but couldn't get to this sooner.

Thanks again!