I need someone to compile lots of historical data of 200 mutual funds into three Motion Graphs that work in Excel. For the data gathering, it will be done through public websites like Yahoo Finance. Because of the amount of data, I am only looking for someone who can write an application that will crawl through the websites and automatically arrange the data onto an Excel spreadsheet.
Let me share with you what I am doing. Watch the video: http://www.youtube.com/watch?v=jbkSRLYSojo Similarly, I’m looking to put mutual fund data into one visualization. Here is a great guide in how to create these Motion Graphs (http://www.s-anand.net/blog/motion-charts-in-excel/); there’s a link to just download the sample Excel worksheet.
I would like to create an analysis of a lot of mutual funds in three Motion Charts. This is the data that is needed (all of this is on Yahoo Finance):
1. Historical monthly price
2. Current Expense Ratio
3. Current R-Squared
4. Calculated Standard Deviation (3 year)
5. Calculated Sharpe Ratio (here’s how to calculate these two: http://corporate.morningstar.com/bf/documents/MethodologyDocuments/MethodologyPapers/StandardDeviationSharpeRatio_Definition.pdf)
GRAPH #1 – Expense Ratio
On the Motion Chart, each “bubble” would represent one mutual fund. The first chart will be to show that over time expensive (i.e. higher “Expense Ratio”) funds under-perform low cost funds. The Y (vertical) axis represents Performance (i.e. month to month price), and the X (horizontal) axis represents Risk (i.e. 3 year Standard Deviation). The size of the bubble represents expense ratio.
We’d take the expense ratio of all the funds and segment out the mutual funds by quartiles. Color the bubbles according to their quartile expense ratio: the highest cost quartile mutual funds Red, the next quartile Orange, the next Blue, and the cheapest Green (so that the high expense ratio funds are shown as bigger/“fat” red bubbles and the low cost funds are smaller/“skinny” green bubbles).
Then in the Motion Graph we start the graph as early as we can (Yahoo Finance typically only goes to the early-mid 1990s, I’d be VERY happy if you can go earlier).
The cheapest ones should show out performance and lower risk. I believe that if we animate it, over time the green bubbles will move to the upper left quadrant (showing relatively lower risk to higher performance) and the expensive bubbles to the lower right (showing relatively higher risk to lower performance).
GRAPH #2 – R-Squared
The second Motion Chart would be to show that index funds outperform managed funds. The way we’d do this is by R-Squared. This one would only have two colors: Blue and Red. The index funds would be the funds with an R-Squared of 99.8-100.0.
Again, we’d run the chart and (hopefully) the index funds would gradually outperform the managed funds.
GRAPH #3 – Sharpe Ratio
We’d use the same coloring of Graph #2 where the index funds are isolated. We’d simply graph it for how the Sharpe Ratio changes over time. I don’t imagine that this chart will have as much movement as the other two. But just that the index funds will “hover” above the other bubbles with a little variation.
As far as specifically what funds, it will be200 of them and I will share this list with you when I award the project. The data would need to be laid out like the Excel document here (http://www.s-anand.net/motion-chart.xls). Thank you.