Month End Activities - Possible to capture dynamically?

[SIMMS7400]

Hi Folks -

For one of my month end close processes, I need to update various applications with the appropriate varibles that depict things like:

1. Current Month
2. Forecast Month
3. Current Year
4. PriorYear
5.Actuals Start Month

etc etc

Obviously, the year values are only performed in the fall for budget season (October is usually when companies begin there FY17 budget) and then in December for annual roll forward.

I currently have a process where I update a text file each month and then it's scheduled on the last day of each month to propogate changes to the necessary applications:

Here is a piece of a text file I update:

Code: Select all

GENJBAPP,Fin_Plan,ActMonth,Oct   
GENJBAPP,Fin_Plan,BudYear,FY17
GENJBAPP,Fin_Plan,CurrMonth,Nov
GENJBAPP,Fin_Plan,CurrYear,FY16
GENJBAPP,Fin_Plan,CurrYear1,FY17
GENJBAPP,Fin_Plan,CurrYear2,FY18
GENJBAPP,Fin_Plan,FcstMonth,Nov
GENJBAPP,Fin_Plan,PriorMonth,Sep
GENJBAPP,Fin_Plan,PriorYear,FY15

The format is as follows:

Application,Database,Substitution Variable,Value

And here is the code I use to perform the updates:

Code: Select all

echo ********************************************************>>%logfile%
echo Update Substition Variables                             >>%logfile%
echo ********************************************************>>%logfile%


>> %maxllogfile% (

FOR   /f "eol=; tokens=1,2,3,4 delims=, " %%i in (Update_Subvars.txt) do (
   
   ::-- Spool most recent values to text file for records --::
   ECHO %%i,%%j,%%k,%%l; >> %date:~-4,4%%date:~-10,2%%date:~-7,2%_%timestamp%_Updated_Subvars.txt

CALL %STARTMAXL% %MAINFOLDER%%MAXLSCRIPTPATH%Update_Subvars.mxl %ESSB_USER% %ESSB_PSWD% %ESSB_SRVR% %%i %%j %%k %%l
)
)

My question is, is there an easy way to grab token 4 of Update_Subvars.txt and run it through a process to understand current value, current system date, and then perform the necessary find and replaces on Update_Subvars.txt?

I'm playing around with some thing like:

Code: Select all

SET CURR_MONTH=%Date:~4,2%
SET CURR_YEAR=%Date:~10,4%

SET CM=%CURR_MONTH%
SET CY=%CURR_YEAR% 


CALL :FND_RPLC Jan Feb
CALL :FND_RPLC Feb Mar
CALL :FND_RPLC Mar Apr
CALL :FND_RPLC Apr May
CALL :FND_RPLC May Jun
CALL :FND_RPLC Jun Jul
CALL :FND_RPLC Jul Aug
CALL :FND_RPLC Aug Sep
CALL :FND_RPLC Sep Oct
CALL :FND_RPLC Oct Nov
CALL :FND_RPLC Dec Jan
CALL :FND_RPLC Jan Feb

IF %CM%==12 (
CALL :FND_RPLC FY15 FY16
CALL :FND_RPLC FY16 FY17
CALL :FND_RPLC FY17 FY18
CALL :FND_RPLC FY18 FY19
)

GOTO SKIP

:FND_RPLC
setlocal enableextensions disabledelayedexpansion
   
set SRC_STRNG=%1
set RPLC_STRNG=%2
set FILE_PATH=%CD%\

set "FILE=%FILE_PATH%Update_Subvars.txt"
   
for /f "delims=" %%i in ('type "%FILE%" ^& break ^> "%FILE%" ') do (
    set "line=%%i"
    setlocal enabledelayedexpansion
    set "line=!line:%SRC_STRNG%=%RPLC_STRNG%!"
    >>"%FILE%" echo !line!
    endlocal
)
GOTO :EOF

But it's pretty barbaric and not sure if its the right approach. Essentially, I need a process to grab column 4 and roll forward 1 month all values except for FY-- strings. FY-- string should be rolled forward ONLY in December. I can perform the intraday year Budget roll forward manually.

Thank you for any and all help!

[dbenham]

Your logic can' work. You only want to replace a given month once. But by doing successive replacements as you have it, it will first change Jan to Feb, and that that new Feb will become Mar, etc.

I believe you have a problem with the notation in your file. You state your budget year runs from Oct through Sep. We are in Oct, so the current budget year is 17, all well and good. You state you want to advance the FY values when changing from Dec to Jan. But FY normally stands for Fiscal Year, which is synonymous with Budget Year. The Fiscal Year should only advance when going from Sep to Oct. The Calendary Year advances when going from Dec to Jan.

I am assuming that the only Fiscal Year you have in your file is BudYear. All the other years are Calendar Years. Your use of FY for calendar year is misleading. If I were to design the file, I would use FY for the one Fiscal Year, and YR for the remaining Calendar Years. But I will preserve the notation that you currently have.

So the rules I will use increment the BudYear going from Sep to Oct, and the other years when going from Dec to Jan.

I'll efficiently solve the problem using JREPL.BAT, a hybrid batch/JScript utility that can do sophisticated regular expression find and replace operations on text files.

Code: Select all

@echo off
setlocal
set "file=Update_Subvars.txt"

:: Define month advancement translations
:: Start out using literal replacement values
set "j="
set "find=Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec"
set "repl=Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan"

:: Add the Budget Year advancement translation if current month is Oct
:: Switch to using JScript replacement values so that we can parse and increment the year
findstr /e "ActMonth,Sep" test.txt >nul && (
  set "j=/j"
  set "find=(BudYear,FY)(\d\d) %find%"
  set "repl=$2+(parseInt($3)+1) '%repl: =' '%'"
)

:: Add the Calendar Year advancement translation if current month is Dec
:: Switch to using JScript replacement values so that we can parse and increment the year
findstr /e "ActMonth,Dec" test.txt >nul && (
  set "j=/j"
  set "find=((?:Curr|Prior)Year\d?,FY)(\d\d) %find%"
  set "repl=$2+(parseInt($3)+1) '%repl: =' '%'"
)

:: Perform all replacements for the entire file in one pass
call jrepl find repl /v /e %j% /t " " /f "%file%" /o -
type "%file%"
exit /b

Dave Benham

[SIMMS7400]

Hi Dave -

Thank you for taking the time to help me!

So, everything is essentially fiscal year. For this particular client, FY is the same as calendar year.

As far as Budget Year, the value for that that subvar is switched in normally Oct as I stated. Therefore, Budget Year FY17 runs from Jan-Dec of 2017.

FY17 is the syntax needed, I do not have the ability to change how to read a year i.e. YR as you stated. The FY-- syntax is what we use then to code calculation scripts within Oracle Hyperion Essbase.

Also, I notice in your code you use a subvar string to know when to switch years. Is there a way around this? Reason being, not all subvars across these systems are named the same.

For instance one system could be ActMonth while another could be ActualMnth. Thats why I originally though the system system would need to be leveraged. So for instance, IF month = 10 (derived from system date), then advance BudgetYear to the next year Or IF month = 12 then advance all years 1.

Now, you're probably wondering, how can I differentiate then between the Budget subvar and the rest? well, luckily, all system will usually start with the lettes Bud so as long as we can search for the string, we'll be ok.

I hope I explained myself well enough? If not, please let me know where you need some color. Thanks, Dave!

[dbenham]

Sure. I made the search ignore case, and I use a negative look-ahead to make sure the attribute name does not begin with "bud".

I use WMIC to get the current month.

Code: Select all

@echo off
setlocal
set "file=Update_Subvars.txt"

:: Define month advancement translations
:: Start out using literal replacement values
set "j="
set "find=Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec"
set "repl=Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan"


:: Add the Budget Year advancement translation if current month is Oct
:: Switch to using JScript replacement values so that we can parse and increment the year
wmic path Win32_LocalTime get month|findstr 10 >nul && (
  set "j=/j"
  set "find=(,Bud[a-z]+,FY)(\d\d) %find%"
  set "repl=$2+(parseInt($3)+1) '%repl: =' '%'"
)

:: Add the Calendar Year advancement translation if current month is Dec
:: Switch to using JScript replacement values so that we can parse and increment the year
wmic path Win32_LocalTime get month|findstr 12 >nul && (
  set "j=/j"
  set "find=(,(?!Bud)[a-z]+,FY)(\d\d) %find%"
  set "repl=$2+(parseInt($3)+1) '%repl: =' '%'"
)

:: Perform all replacements for the entire file in one pass
call jrepl find repl /v /i /e %j% /t " " /f "%file%" /o -
type "%file%"
exit /b

Dave Benham

[SIMMS7400]

Hi Dave -

Thank you so much!

I'm running the process now and it's working as expected. This is great! Thank you so much!

[dbenham]

I think there is a bug in the logic that I gave you.

If the budget year date should advance in Oct, then I think the calendar year dates should advance in Jan.

If the calendar year dates advance in Dec, then I think the budget year date should advance in Sep.

I'll let you figure it out, and adjust the code as needed.


Dave Benham

[SIMMS7400]

Hi Dave -

Very good, I will see what I can find! Thank you!

[SIMMS7400]

I think there is a bug in the logic that I gave you.

If the budget year date should advance in Oct, then I think the calendar year dates should advance in Jan.

If the calendar year dates advance in Dec, then I think the budget year date should advance in Sep.

I'll let you figure it out, and adjust the code as needed.


Dave Benham

Hi Dave -

The previous code you provided is working perfectly! I have a few exceptions I was hoping you could help me account for?

Quarters are defined as Q1.

For instance the value of CurQtr is Q4.
CurMo would be changed without modification using your code above. However, there now exists from additional checks I need to perform. Is this able to be captured?

Code: Select all

CurMo     change always
CurQtr     change if CurMo sits in new Qtr
PreQtr     change if CurQtr moves forward
NextMo     change always unless CurMo is Dec
NextMo2   change always unless CurMo is Nov or Dec
NextQtr   change if CurMo sits in new Qtr
NextQtr2  change if CurMo sits in new Qtr except for Q4 months value stays Q4

So it looks like we'll need to hard code some specific variable names above in order to achieve some of these requirements, which is fine. I have multiple other variables not shown above that have been working fine with the latest code which rolls months forward automatically, and only rolls FYXX values forwards in December (unless it's the budget variable).

Thank you, Dave! I'm hoping this is attainable.

[SIMMS7400]

HI Dave -

I was wondering if you help me with establishing conditions as I have outlined above? If you give me some direction, I'll then see if I can complete it. Thanks!

[SIMMS7400]

Hi Dave -

Sorry to beat a dead horse, just wanted to revisit this this. Everything is working as expected I just have a few tweaks:

For the portion of syntax that advances the year substitution variables values (i.e. FY16, FY17 etc), we need that to be advanced, regardless of what substitution variable it is, EXCEPT any substitution variables with Bud string in their name. I think I confused you and set the substitution variables in Dec also had the string "Bud" in them, but that's not the case.

For instance, in December, the substitution variables containing FY** values will be advanced from :

Code: Select all

DIFIN,DIFIN,CurYr,FY16
DIFIN,DIFIN,NextYr,FY17
DIFIN,DIFIN,NextYr2,FY18
DIFIN,DIFIN,NextYr3,FY19
DIFIN,DIFIN,NextYr4,FY20
DIFIN,DIFIN,NextYr5,FY21
DIFIN,DIFIN,PemCY,FY16
DIFIN,DIFIN,PemNY,FY17
DIFIN,DIFIN,PreYr,FY15
DIFIN,DIFIN,PreYr2,FY14
DIFIN,DIFIN,PreYr3,FY13

to

Code: Select all

DIFIN,DIFIN,CurYr,FY17
DIFIN,DIFIN,NextYr,FY18
DIFIN,DIFIN,NextYr2,FY19
DIFIN,DIFIN,NextYr3,FY20
DIFIN,DIFIN,NextYr4,FY21
DIFIN,DIFIN,NextYr5,FY22
DIFIN,DIFIN,PemCY,FY17
DIFIN,DIFIN,PemNY,FY18
DIFIN,DIFIN,PreYr,FY16
DIFIN,DIFIN,PreYr2,FY15
DIFIN,DIFIN,PreYr3,FY14

Obviously any substitution variables names containing the string Bud will be untouched.


I tried a bunch of different things with no luck. One of them being a * in hopes to capture all variables with FY in their values:

Code: Select all

wmic path Win32_LocalTime get month|findstr 12 >nul && (
  set "j=/j"
  set "find=(,(?!*)+,FY)(\d\d) %find%"
  set "repl=$2+(parseInt($3)+1) '%repl: =' '%'"
)

Also, could you help me advance substitution variables pertaining to quarters?

For instance, in December they will go from :

Code: Select all

DIFIN,DIFIN,CurQtr,Q4
DIFIN,DIFIN,NextQtr,Q1
DIFIN,DIFIN,NextQtr2,Q2
DIFIN,DIFIN,PreQtr,Q3

to

Code: Select all

DIFIN,DIFIN,CurQtr,Q1
DIFIN,DIFIN,NextQtr,Q2
DIFIN,DIFIN,NextQtr2,Q3
DIFIN,DIFIN,PreQtr,Q4

For this case, I can't be sure all substitution variables will include a string that pertains to quarter. Therefore, for here, we'd need to just search on the value as they will always have "Q" in them.

Thank you, Dave!

[SIMMS7400]

Hi Dave -

I've been able to meet my requests. I will continue to read up on the various threads on how to better use jrepl. As you can see, I use additional find & replaces to correct certain advancements.

I assume I could wrap advancements as well as exceptions into one statement? I will dig further. In the mean time, here is the code that is currently working for me:

Code: Select all

@echo off
setlocal

set "file=Update_Subvars.txt"

::-- Define month advancement translations --::
::-- Start out using literal replacement values --::
set "j="
set "find=Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec"
set "repl=Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan"


::-- Add the Budget Year advancement translation if current month is Oct --::
::-- Switch to using JScript replacement values so that we can parse and increment the year --::
IF %date:~-10,2%==10 (
::wmic path Win32_LocalTime get month|findstr 10 >nul && (
  set "j=/j"
  set "find=(,Bud[a-z]+,FY)(\d\d) %find%"
  set "repl=$2+(parseInt($3)+1) '%repl: =' '%'"
)

::-- Add the Calendar Year advancement translation if current month is Dec --::
::-- Switch to using JScript replacement values so that we can parse and increment the year --::
IF %date:~-10,2%==01 (
::wmic path Win32_LocalTime get month|findstr 1 >nul && (
  set "j=/j"
  set "find=(,FY)(\d\d) %find%"
  set "repl=$2+(parseInt($3)+1) '%repl: =' '%'"
)

::-- Define Quarter advancement translations --::
::-- Start out using literal replacement values --::
::-- Add /G: for file with months for Quarter advancement ^: 4 7 10 1 --::

IF %date:~-10,2%==01 (
::wmic path Win32_LocalTime get month|findstr 1 >nul && (
set "k="
set "find1=Q1 Q2 Q3 Q4"
set "repl1=Q2 Q3 Q4 Q1"
)

::-- Start of Capture Exceptions --::
::-- All months will roll forward, so need to roll back advancacements in Nov/Dec for certain subvars --::
::-- NexMo ^& NexMo2 CAN NOT roll forward to the new FY unless CurMo is in the new FY --::
::-- When CurMo=Nov NexMo=Dec NextMo2=Dec --::
IF %date:~-10,2%==11 (
::wmic path Win32_LocalTime get month|findstr 11 >nul && (
set "l="
set "find2=NexMo2,Jan"
set "repl2=NexMo2,Dec"

)

::-- NexMo ^& NexMo2 CAN NOT roll forward to the new FY unless CurMo is in the new FY --::
::-- When CurMo=Dec NexMo=Dec NextMo2=Dec --::
IF %date:~-10,2%==12 (
::wmic path Win32_LocalTime get month|findstr 12 >nul && (
set "l="
set "find2=NexMo,Jan NexMo2,Jan"
set "repl2=NexMo,Dec NexMo2,Dec"

)

::-- Since CurMo, NexMo, ^& NextMo2 will advance Jan, need to rest NextMo ^& NextMo2 to normal advancement increments--::
IF %date:~-10,2%==01 (
::wmic path Win32_LocalTime get month|findstr 1 >nul && (
set "l="
set "find2=NexMo,Jan NexMo2,Jan"
set "repl2=NexMo,Feb NexMo2,Mar"

)
::-- End of Capture Exceptions --::
::-- Perform all replacements for the entire file in one pass --::
call C:\TEST\jrepl find repl /v /i /e %j% /t " " /f "%file%" /o -
call C:\TEST\jrepl find1 repl1 /v /i /e %k% /t " " /f "%file%" /o -
call C:\TEST\jrepl find2 repl2 /v /i /e %l% /t " " /f "%file%" /o -
type "%file%"

exit /b

Here is the file I'm advancing:
Let's say I am advancing today for November month end:

Code: Select all

DIFIN,DIFIN,CurMo,Oct
DIFIN,DIFIN,CurQtr,Q3
DIFIN,DIFIN,FMVFXMth,Dec
DIFIN,DIFIN,NexMo,Nov
DIFIN,DIFIN,NexMo2,Dec
DIFIN,DIFIN,NexQtr,Q4
DIFIN,DIFIN,NexQtr2,Q4
DIFIN,DIFIN,PemCM,Oct
DIFIN,DIFIN,PemNM,Nov
DIFIN,DIFIN,PreMo,Sep
DIFIN,DIFIN,PreMo2,Aug
DIFIN,DIFIN,PreQtr,Q2
DIFIN,DIFIN,CurYr,FY16
DIFIN,DIFIN,NexYr,FY17
DIFIN,DIFIN,NexYr2,FY18
DIFIN,DIFIN,NexYr3,FY19
DIFIN,DIFIN,NexYr4,FY20
DIFIN,DIFIN,NexYr5,FY21
DIFIN,DIFIN,PemCY,FY16
DIFIN,DIFIN,PemNY,FY17
DIFIN,DIFIN,PreYr,FY15
DIFIN,DIFIN,PreYr2,FY14
DIFIN,DIFIN,PreYr3,FY13

Here's the file after the code runs:

Code: Select all

DIFIN,DIFIN,CurMo,Nov
DIFIN,DIFIN,CurQtr,Q3
DIFIN,DIFIN,FMVFXMth,Jan
DIFIN,DIFIN,NexMo,Dec
DIFIN,DIFIN,NexMo2,Dec
DIFIN,DIFIN,NexQtr,Q4
DIFIN,DIFIN,NexQtr2,Q4
DIFIN,DIFIN,PemCM,Nov
DIFIN,DIFIN,PemNM,Dec
DIFIN,DIFIN,PreMo,Oct
DIFIN,DIFIN,PreMo2,Sep
DIFIN,DIFIN,PreQtr,Q2
DIFIN,DIFIN,CurYr,FY16
DIFIN,DIFIN,NexYr,FY17
DIFIN,DIFIN,NexYr2,FY18
DIFIN,DIFIN,NexYr3,FY19
DIFIN,DIFIN,NexYr4,FY20
DIFIN,DIFIN,NexYr5,FY21
DIFIN,DIFIN,PemCY,FY16
DIFIN,DIFIN,PemNY,FY17
DIFIN,DIFIN,PreYr,FY15
DIFIN,DIFIN,PreYr2,FY14
DIFIN,DIFIN,PreYr3,FY13

Thanks for jrepl! Works great!