Hi All - I am new to Alteryx i am trying to do small calculation using alteryx. I want help in designing work flow. I have monthly data which i need to annualized returns every month - the formula used in excel is
{=PRODUCT(C$2:C14+1)^(12/COUNT(C$2:C14))-1}
I need a summary of data in below format which is were i am stuck and this table will be updated on month to month basis as when the data gets added to input columns
1 Mo (%) | 3 Mo (%) | YTD (%) | 1YR (%) | 3YR (%) | 5YR (%) | 7 YR (%) | 10 YR (%) | SI (%) | |
Input 1 | |||||||||
Input 2 | |||||||||
Input 3 | |||||||||
Input 4 |
I want 1 month data to be latest data example= Nov month returns and rest with formula
Any help on above topic
I'm not 100% sure I get exactly what you are looking for.
The workflow below will assign the value per different category for the time period for each input column.
Please let me know if this was what you were looking for.
Pedro.
Hi @prveenk86kumar ,
hope I got you right. In my understanding, you want to calculate the Excel matrix formula for each input column and the defined period ranges based on the period you specified (currently Nov 2020).
You could start with an Text Input tool to define your current period (if this is not the last month). Based on this, you could calculate the start periods for the ranges and add this identifier to your data. The next steps are intended to calculate the base value (e.g. product of returns for last year). Based on this results, you can calculate the final result for each period range (did this only for some of the ranges)
Let me know if this is helpful.
Best,
Roland
Thanks for the solution partial its working, I had couple questions or help which i would require in completing this workflow.
1) YTD - needs to be rolling 12months returns which should be {=PRODUCT(C$2:C14+1) currently its not working could please help me on this?
3)Also i need to build formulas for 1y,3y,5y,7y and inception data using below formula, i tried using it but workflow is failing
1y ={=PRODUCT(C$2:C14+1)^(12/12-1}
3y ={=PRODUCT(C$2:C14+1)^(12/36)-1}
5y={=PRODUCT(C$2:C14+1)^(12/60)-1}
7y={=PRODUCT(C$2:C14+1)^(12/84)-1}
total return = {=PRODUCT(C$2:C14+1)^(12/COUNT(C:C))-1}
Hi @prveenk86kumar ,
I've added to calculation for missing period ranges.
Basically, the Multi-Row Formula tools calculate the first part of your Excel formula for the specific period ranges (i.e. the =PRODUCT(C$2:C14+1) part), while the formula tool does the "annualization".
Have a look and let me know, if it works as expected.
Best,
Roland
Hi Roland, Can you helpme on the below solution
Could you help me on below issue