Trying to do the EXCEL Product Function Based on a Date Range

Hi all,

I'm trying to calculate a cumulative return using a function like EXCEL PRODUCT only if the return falls on a date range.

For example, if the "date" is 1/5/2022 and the "period ago date" is 1/1/2022, I'd like to do the EXCEL product function for all the returns from 1/1/2022 to 1/5/2022. I AM NOT looking for a rolling period calculation, but rather a calc based on the date range.

The same will apply for the next row, but just a shift in date rage (from 1/2 to 1/6), and so on. Ideally, this would be output into a new column titled "Period Ago Cumulative Return." Will be doing this on a larger scale of data. How can I best accomplish this?

Cumulative Rtn Based on Date Range...Sample Data.xlsx

Reporting

Output

Preparation

Iterative Macro

Accepted answers

philaw99

Hi,

Think it makes sense. I see how you were able to arrive at the numbers. I'm actually having trouble replicating this for a larger data set because some of the "PeriodAgo" dates are NULL() and default to 9999-12-31, so that when I use the generate rows tool im getting the "DateTimeadd (9999-12-31, 1 days) goes outside the supported range of dates" error.

Any insight you can add to the top? Would you be able to provide a solution for an iterative macro as well if the top doesn't pan out? Thanks for all the help!

All comments

apathetichell

There's a bunch of different ways to approach this... I took this as an opportunity to play around with some of the log functions...

power of logs.yxzp

philaw99

Hi,

Thanks for helping with this! A little confused about this logic as I am not familiar with log/generate rows tool. Can you help explain why there is a log function and what the generate rows tool is doing?

Also, I notice that your final output is different than what I am looking for. I've attached an updated copy with a "test" column in the sample data above.

apathetichell

Multiplying your returns from 1/1 to 1/5 gave me

1.094958

In Alteryx I got: 1.094957 ---I don't see your Test column sorry.

Alteryx is a bit different than Excel. First off - to create the range, I use generate rows to create a set of dates for each entry in your date range (ie the first date to the last date). I then re-join this to the original data source to get the daily return for each day in the range. Armed with a match key, a range of dates related to that key and the returns for those days - I then have an issue - I can create some kind of iterative macro to multiple the numbers. I can use a python array thingy. I can use multi-row formula to set a base value and then just multiple or I can do something super cool by transforming the returns into logs and then adding them. This works because - MATH. You can do the same thing in Excel btw by using LOG for you D column and getting

0.002166

0.040207

-0.00218

-0.00305

0.002252

You can then use =10^(cell with the sum of the logs) to get the value... Does that make sense?

philaw99

Hi,

Any insight you can add to the top? Would you be able to provide a solution for an iterative macro as well if the top doesn't pan out? Thanks for all the help!

apathetichell

Sure! But just to be clear - I'm catching the Null() exception - it's the period ago at 9999-12-31 that I don't catch... check out the intilization expression in the generate rows tool and add an "and [Period Ago Date]!="9999-12-31" - basically this sets that specific date to be equal to the original date - so the generate rows iteration doesn't happen.

If you are still getting an error after this - it's because you need to clean your dates (ie you have a string value in your date field)... That will adjust for null()s and 9999-12-31...I just tested it.

Can you mark the solution as correct -thanks!

philaw99

Thank you - I've made the correction you suggested but now im getting an error titled "invalid type as operator..."

I've checked all my fields beiing used in the tool and they are all Date fields. Any suggestions here?

apathetichell

Can you add a select tool somewhere early on? I think one of your dates might not be date- it would be solid to convert them to a date before the generate rows tool.

apathetichell

also make sure you don't have too many or too few quotes

philaw99

Is there anything off you see with the above expression?

apathetichell

if [Rolling12MAgo] != null() then [Rolling12MAgo] else [ActivityDate] endif and [Rolling12MAgo] != '9999-12-31'

should be

if [Rolling12MAgo] != null() and [Rolling12MAgo] != '9999-12-31' then [Rolling12MAgo] else [ActivityDate] endif

sorry - two and clauses...

philaw99

Hi,

Now I'm running into the "DateTimeAdd (9999-12-31,1 days) goes outside the supported range of dates" error after using your new function.

Double checked that all incoming fields for this Generate Rows tool are date fields. Any further advice?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703