How to divide the covariance by the variance in alteryx? Is there a formula for that?

[Joker_Hazard]

Hello guys.

I have been struggling to create a way to take the covariance matriz and divide by the variance of a investment fund. It works just like stock market with company A and company B.

The goal is to create this formula in alteryx:

Its a very famous formula in stock market. In excel we can easily do this by using their formulas, but alteryx does not have in finance formulas (in formula tool).

Just a reminder I could also have 10 stocks for example, so it would be a matriz that is 10 x 10.

I also created this macro that does the covariance.

Any thoughts?

Thanks!

[apathetichell]

Beta! Have you tried the pearson tool in calculate covariance mode? you can calculate variance with the summarize tool and then append and divide them.

[atcodedog05]

Hi @Joker_Hazard 

Can you share your macro? Would like to take a look into it 🙂

[atcodedog05 · Accepted solution]

Hi @Joker_Hazard 

You can formula like below if its more than 2 variables.

Workflow:

1. Select all data columns in pearson tool and configuring to calculate covariance.

2. Using transpose tool to convert columns to rows. And then using summarize tool groupby on column and calculate variance i am doing this to keep the number of columns selected dynamic.

3. Using join tool to map variance of the fieldname.

4. Using multi-field formula to find covraiance(a,b)/varaince(a) 

I dont know how near it's to the requirement. Hope this helps : )

[Joker_Hazard]

Sure! Feel free to check the covariance tool. I did not know that we had pearson tool and it worked the same way as this one. Looks like I wasted time HEHE.

Your solution looks awesome, just let me ask a question, is it possible to automate the multifield tool as if the companies that we chose in the input tool are the same as in the multi field tool?

I mean, in my case I would have 1500 companies, so I would be very nice if I did not have to singularly flag each one of them in all tools.

The same with pearson tool.

Thanks in advance!

[atcodedog05]

Hi @Joker_Hazard 

Dynamic or unknown column selection is checked in all tools. It should automatically pickup all columns 🙂

Thank you for sharing the macro 🙂

[Joker_Hazard]

Sure! I'm the one that should thank you 🙂

Best regards from Brazil!!