Importing Historical Stock Prices from Yahoo into Excel
This Excel spreadsheet imports historical stock prices from Yahoo Finance (www.cronistalascolonias.com.ar). You don&#;t need to manually import data with text files. You simply need to specify a ticker symbol, your start and end dates, and then click a button.
Once you click the button, Excel (after calling a VBA function) connects to Yahoo Finance and retrieves daily stock quotes, placing the data into a sheet called &#;Data&#;
Currently, the spreadsheet imports the date (in day/month/year format), opening price, daily high, daily low, closing price, trading volume and the adjusted close. All prices are in US dollars.
You&#;re free to manipulate and analyze the data once it&#;s in Excel. As an example of some trivial analysis, clicking a button in the spreadsheet gives the arithmetic mean, the variance and the standard deviation of the daily returns (based upon the daily closing price).
You could also perform far more advanced analyses (which I&#;ll discuss in future blog posts). For example, I&#;ve used this variations of this spreadsheet to calculate investment ratios (e.g. Sharpe, Sortino and Treynor Ratios), plot histograms of daily returns to check if they&#;re normally distributed, identified stocks with my desired risk characteristics, and calculate mean-variance optimal portfolios.
The VBA that retrieves the data from Yahoo was adapted from code found at www.cronistalascolonias.com.ar It can be modified to download other data found on the web.
You&#;ll need to enable macros before you use the spreadsheet (Excel may warn you that you&#;re importing a macro-enabled worksheet).
UPDATE July 17th Here&#;s an Excel spreadsheet that downloads historical Forex rates for two currency pairs between two dates
UPDATE 18th August Here&#;s a Mathcad worksheet to download stock quotes from Yahoo Finance
UPDATE 14th February Download historical quotes into Excel for multiple stock tickers here
UPDATE 5th January I&#;ve just evaluated a financial data add-in for Excel MarketXLS. It&#;s a rock-solid route to getting live stock data inside Excel. You just use one of 80 new functions together with your chosen ticker. Disclaimer: the link to MarketXLS is an affiliate link, but this has not influenced my opinion of the tool.
UPDATE July 6th The old Yahoo Finance API was discontinued, so the spreadsheet stopped working for a while. However, I&#;ve now uploaded a new working version
Download Excel spreadsheet to import historical stock prices from Yahoo
-
-
-