Tax, Audit + Office of Finance

SOLVED

Portfolio Management - Calculation of portfolio standard deviation and beta

rsomani005
8 - Asteroid

Has anyone used Alteryx to calculate portfolio standard deviation and beta? Any ideas on how to make these computations given a historical data set.

11 REPLIES 11
RishiK
Alteryx
Alteryx

Hi there

 

I noticed that no one has responded to you on this. Is the standard deviation to be calculated on the population? If so, check this macro out:

 

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Standard-Deviation-Population-Macro/ta-p/302...

 

Also, there is a very interesting Portfolio Optimization workflow (at the bottom of the Community Article below) you must check out related to Portfolio Management, etc:

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Mean-Variance-Optimisation/td-p/224718

 

 

rsomani005
8 - Asteroid

Thanks a lot ! This is exactly what I was looking for. 

RishiK
Alteryx
Alteryx

No problem. Glad it helped. Keep them coming!

rsomani005
8 - Asteroid

Thanks following help from you, I have been able to connect the workflow to download data from Quandl through API and find optimal weights as per MPT. Will try to back test this with historical data and see how these recommended weights perform historically.

RishiK
Alteryx
Alteryx

Hi @rsomani005 

 

I am glad you progressed. Its all about the Community so do keep posting :) and get others to do so also!

RishiK
Alteryx
Alteryx

Hi @rsomani005 would you be able to share your workflow for the Community if possible?

rsomani005
8 - Asteroid

Dear All,

 

If you have followed the earlier messages on this thread, I had started out with an initial problem statement of calculating portfolio standard deviation. Over last couple of weeks my problem statement evolved to identifying optimal weights for a given set of stocks when provided for information about their returns and volatility. With help from the community I was able to build two workflows which solved this problem.

 

I am attaching the first workflow here "Workflow_01_Weekly Stock Price_Quandl.yxmd" with an embedded macro "Macro_01_Stock Price_Quandl.yxmc". This workflow downloads weekly stock price data from Quandl. You can specify the Quandl codes and will also need to enter your API key for Quandl to be able to make this work for yourselves.

 

Once you download this weekly stock price data, you need to convert this into weekly returns and input this in the second workflow (Workflow_00_MPT_24Nov18_Upload.yxmd"). Following this you need to enter your constraints in the (ub, lb), in the stream which is connecting to Anchor O of the optimization icon in the workflow. The outputs on the browse tool should give you the optimal stock weights. 

 

This is my first post, so please accept my apologies if my explanation is lacking somewhere. Also credits to the community where these workflows already existed and I merely have deployed it for my personal wealth management usage. Some other conceptual limitations from a finance perspective which Immediately come to my mind are that I have not adjusted for dividends / stock splits and to that extent my analysis will be deficient. Yet to figure out a way / database which gives data corrected for dividends / stock splits. Hopefully this will evolve over time. 

 

Cheers

mutama
Alteryx Alumni (Retired)

Hi rsomani005 

 

Thank you so much for these resources! May I ask whether you can post the workflow as a packaged Alteryx file (in .yxzp format)? 

 

So that we can ingest the data and run the workflow. Looks impressive!

 

Best,

Michael

rsomani005
8 - Asteroid

The workflows are attached in the post on top.