Download historical stock quotes from Google Finance straight into Excel with this free spreadsheet. Everything is automated with some clever VBA (which you can view and extend.)
Once you have the stock data, you can analyze it, conduct historical backtests and more. As an example, the VBA in the spreadsheet calculates the mean daily return, standard deviation and the variance.
The spreadsheet is simple to use. Just supply the ticker symbol, start date and end date. After you click a button, Excel uses a VBA macro to connect to Google Finance to import the data.
The spreadsheet imports the trading date, open price, high price, low price, close price, trading value and the adjusted closed. The data is downloaded into a worksheet called “Data”.
The VBA can be viewed and modified. In Excel 2010, go to Developer > Visual Basic and then find Module 1 in the Project Explorer.
Other spreadsheets to download finance data from the web can be found here.