MS Excel can sort historical stock data off finance.yahoo.com. Though limited, the tools available provides average investors an advantage over those new to the game. I will expand on this in the days to come.
Here's a quick guide. I'll try and upload pictures to simplify the steps further.
1) OK, first you must get your data off finance.yahoo.com
I will type in "spy" for the S&P based ETF, and press "enter".
2) Once on the SPY page, you'll find the link for "Historical Prices" on the left side pane. Click on it.
3) Then scroll down and click "Download to Spreadsheet"
4) Choose MS Excel to open up the downloaded file.
5) Now comes the tricky part. Yahoo Finance typically provides the data from the most recent til the earliest. For adequate analysis, we need the most recent at the bottom of the page. To do this, first find the number of data, then key the first two in a new column, starting at the highest.
E.g. I have 3,824 days of historical data imported, so next to the latest 2 dates, I keyed in 3,824 and 3,823.
6) Now highly both numbers, and click at the bottom right hand corner of the highlighted box. It will do an automatic completion of the numbers for you automatically.
7) Then highlight all involved columns, and click on "sort", A-Z. And voila, the numbers are all better.
(Send me a message if you have trouble getting this done, and I can send you my sample excel file.)
I will post some simple statistical edges you can gain via excel based analysis in the next few days. Good luck.
4 months ago
2 Reflections:
I may be missing something here, but couldn't you just sort the dates from A-Z?
I think Office 2007 has some new options that makes that step more simple. So you're probably right.
I'm still on Office 2003, didn't like how bulky the latest version is and the amount of system resources demanded.
Post a Comment