using Excel VBA, given the daily price of 50 stocks, choose 10 stocks such that they have the minumu

Posted by correl on Stack Overflow See other posts from Stack Overflow or by correl
Published on 2010-04-15T15:51:38Z Indexed on 2010/04/15 15:53 UTC
Read the original article Hit count: 491

Filed under:

stocks

|

stockquotes

|

correlation

|

excel

|

vba

The high-level goal is to choose 10 stocks that have the lowest correlation among one another, out of a pool of 50, so that I can have a well-diversified portfolio.

I have managed to write some VBA macro to download the past 3 years of daily price data from Yahoo finance, and then compute the 50x50 correlation matrix (using the Correl function), using the daily close as the data.

What I have tried so far is just some local-maximum heuristic: - For the two stocks that have the highest correlation with each other, remove one of them. Between the two, remove the one that has the higher average correlation with all the other stocks. - When I remove a stock from the pool, I just delete the correponding row and column, to give a smaller matrix. - Repeat until I have just 10 stocks remaining (a 10x10 matrix).

I was wondering if there is some algorithm that already solves such a problem and gives the optimum solution?

© Stack Overflow or respective owner

Related posts about stocks

Launch Apple's Stocks app, with a particular stock selected

as seen on Stack Overflow - Search for 'Stack Overflow'
I would like to launch Apple's Stocks app to show information for a particular stock, on a non-jailbroken phone. I'm not interesting in how to get a quote or graph a stock myself, just opening Stocks.app. I was hoping that the Stocks app would have a custom URL format, so opening a URL like stocks://AAPL… >>> More
using Excel VBA, given the daily price of 50 stocks, choose 10 stocks such that they have the minumu

as seen on Stack Overflow - Search for 'Stack Overflow'
The high-level goal is to choose 10 stocks that have the lowest correlation among one another, out of a pool of 50, so that I can have a well-diversified portfolio. I have managed to write some VBA macro to download the past 3 years of daily price data from Yahoo finance, and then compute the 50x50… >>> More
How to get the data for intra-day candlestick charts for stocks on eg Nasdaq

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, For a learning exercise, i'm wanting to create candlestick (stock) graphs for stocks using zedgraph. Now on google finance, i can get daily open-high-low-close data which is perfect for making these graphs, but i'm wanting to create intra-day graphs, eg open-high-low-close data for an hour (or… >>> More
Stocks with Ext JS Charts

as seen on Code Project - Search for 'Code Project'
An example to display stock indexes in an Ext JS chart. Including an introduction to Ext JS, a simple introductory Ext JS example, and an introduction to the new charts feature. Concludes with a more comprehensive demo showing some more of Ext’s features. >>> More
How to know if two stocks move togheter?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, I have two stocks with their prices, example: STOCK1: 10.56 11.23 12.32 8.90 STOCK2: 1.26 5.80 3.26 10.3 I only found Pearson correlation, but, is there another method to know if two stocks move togheter? (esample: co-integration??) Thank you so much! >>> More

Related posts about stockquotes

Accessing Yahoo realtime stock quotes

as seen on Stack Overflow - Search for 'Stack Overflow'
There's a fairly easy way of retrieving 15-minute delayed quotes off of Yahoo! Finance web site ("quotes.csv" API). However, so far I was unable to find any info on how to access real-time quotes. The hang-ups with real-time quotes are: Only available to logged-in user No API Non-obvious how to… >>> More
using Excel VBA, given the daily price of 50 stocks, choose 10 stocks such that they have the minumu

as seen on Stack Overflow - Search for 'Stack Overflow'
The high-level goal is to choose 10 stocks that have the lowest correlation among one another, out of a pool of 50, so that I can have a well-diversified portfolio. I have managed to write some VBA macro to download the past 3 years of daily price data from Yahoo finance, and then compute the 50x50… >>> More
Where can I get free real-time stock data?

as seen on Stack Overflow - Search for 'Stack Overflow'
Does anyone know of a way to obtain free real-time stock data or near real-time stock data? I'd like to do this since I'm interested in the financial market, not for use in investment applications which is why I'm looking for something free. I've tried the Perl module Finance::YahooQuote but some… >>> More
Custom ASP.NET Routing to an HttpHandler

as seen on West-Wind - Search for 'West-Wind'
As of version 4.0 ASP.NET natively supports routing via the now built-in System.Web.Routing namespace. Routing features are automatically integrated into the HtttpRuntime via a few custom interfaces. New Web Forms Routing Support In ASP.NET 4.0 there are a host of improvements including routing support… >>> More
M-V-VM, isn't the Model leaking into the View?

as seen on Stack Overflow - Search for 'Stack Overflow'
The point of M-V-VM as we all know is about speraration of concerns. In patterns like MVVM, MVC or MVP, the main purpose is to decouple the View from the Data thereby building more flexible components. I'll demonstrate first a very common scenario found in many WPF apps, and then I'll make my point: Say… >>> More