While you might[/I] be able to accomplish this with Monarch, though I haven't entirely convinced myself that it's possible, I believe that developing the model would require a lot of effort, and achieving the same end result with Excel would be much easier. Hopefully you're not using Excel 2007 as I don't know if what follows here will work in that case.
By the sounds of this, you want an on-going month over month comparison analysis. So you'll only ever have a row of heading, and two rows of monthly total amounts.
Export the Month 1 data to a Excel file (overwrite existing file), then append the Month data 2 with a separate project export.
Now open a new Excel file, and hit Alt-F11 to go to the Visual Basic Editor
On the left side of the window you should see the VBAProject /Bfor your new file. Double-click the ThisWorkbook item. Into the white space on the right, paste this code:
Workbooks.Open Filename:= _
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yy;@"
ActiveCell.FormulaR1C1 = "Difference"
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Selection.Style = "Comma"
Most of this was created with Excel's macro recorder, so it's not the best code ever written, but we won't worry about such things for such a small use.
The only thing that you'll need to do is change the file path and name of your export file in the code, and make changes to accomodate for your 20 columns. But the approach is the same.
The program code will run automatically every time you open this file. Quick and hands-free.
Or we can continue to attempt a Monarch only solution.
Hi Kruncher -
Thank you for your help and the great VBScript info. It is working perfectly after some minor tweaking.
Unfortunately, we currently run 3 projects to get the summary to this point, and we do this for a couple hundred customers every month. So, the more I can get automated with Monarch, the happier I am (especially if we can get datapump going soon!).
Do you know if Datapump can run the excel macros like this one?