For years Jack has used the Yahoo! Finance API to import stock information into his worksheets.
We would like to show you a description here but the site won’t allow us. 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.
Now that service has gone away. Jack figures he can't be alone in needing to get stock information into his Excel worksheets, so he is wondering if there other services that folks with similar needs can suggest. There are a few ways that the loss of the Yahoo! Finance data could be approached. If you need just current prices, one approach is to rely on Google Sheets. As, you can use the GoogleFinance function to get a number of financial statistics. Once they are in a Sheets worksheet, you can then copy and paste them into your Excel worksheet.
There are also other sites that provide data and include an API. You might need to write your own code to access the data through the API, but it is available. Here are two sites to check out: If you poke around on the sites, you may find that some data is offered in Excel format, as well. One subscriber suggested the following site as a possibility: You may want to poke around in the comments on the page, as some seem to indicate some trouble in getting the financial information they desired. Actually the Yahoo!Finance API is not dead. It is just changed and Yahoo will not acknowledge it or support any attempt to resurrect the old methodology. Take a look at: I have modified his code for my purposes but it does work, not as seamlessly as before, ie, I have found that simply entering 1/1/1950 as the starting date may not get you all dates of a particular stock so you might have to look on Yahoo!Finance to see what is available.
Fetches current or historical securities information from Google Finance. Sample Usage GOOGLEFINANCE('GOOG', 'price', DATE(2014,1,1), DATE(2014,12,31), 'DAILY') GOOGLEFINANCE('GOOG','price',TODAY-30,TODAY) GOOGLEFINANCE(A2,A3) Syntax GOOGLEFINANCE(ticker, attribute, startdate, enddate numdays, interval).
ticker - The ticker symbol for the security to consider. Note: Reuters Instrument Codes are no longer supported. For example, ticker 123.TO or XYZ.AX would not work. Instead, use TSE:123 or ASX:XYZ. Recommended: Add an exchange to avoid discrepancies. For example, use “NASDAQ:GOOG” instead of “GOOG.'
If an exchange is not specified, GOOGLEFINANCE will use its best judgement to choose one for you. attribute - OPTIONAL - 'price' by default - The attribute to fetch about ticker from Google Finance. This is required if a date is specified. attribute is one of the following for realtime data:. 'price' - Realtime price quote, delayed by up to 20 minutes. 'priceopen' - The price as of market open. 'high' - The current day's high price.
'low' - The current day's low price. 'volume' - The current day's trading volume. 'marketcap' - The market capitalization of the stock. 'tradetime' - The time of the last trade. 'datadelay' - How far delayed the realtime data is. 'volumeavg' - The average daily trading volume. 'pe' - The price/earnings ratio.
'eps' - The earnings per share. 'high52' - The 52-week high price. 'low52' - The 52-week low price. 'change' - The price change since the previous trading day's close. 'beta' - The beta value. 'changepct' - The percentage change in price since the previous trading day's close. 'closeyest' - The previous day's closing price.
'shares' - The number of outstanding shares. 'currency' - The currency in which the security is priced. Currencies don't have trading windows, so open, low, high, and volume won't return for this argument. attribute is one of the following for historical data:.
'open' - The opening price for the specified date(s). 'close' - The closing price for the specified date(s). 'high' - The high price for the specified date(s). 'low' - The low price for the specified date(s). 'volume' - The volume for the specified date(s).
'all' - All of the above. attribute is one of the following for mutual fund data:. 'closeyest' - The previous day's closing price.
'date' - The date at which the net asset value was reported. 'returnytd' - The year-to-date return. 'netassets' - The net assets.
'change' - The change in the most recently reported net asset value and the one immediately prior. 'changepct' - The percentage change in the net asset value. 'yieldpct' - The distribution yield, the sum of the prior 12 months' income distributions (stock dividends and fixed income interest payments) and net asset value gains divided by the previous month's net asset value number.
'returnday' - One-day total return. 'return1' - One-week total return. 'return4' - Four-week total return. 'return13' - Thirteen-week total return.
'return52' - Fifty-two-week (annual) total return. 'return156' - 156-week (3-year) total return.
'return260' - 260-week (5-year) total return. 'incomedividend' - The amount of the most recent cash distribution. 'incomedividenddate' - The date of the most recent cash distribution.
'capitalgain' - The amount of the most recent capital gain distribution. 'morningstarrating' - The Morningstar 'star' rating. 'expenseratio' - The fund's expense ratio. startdate - OPTIONAL - The start date when fetching historical data. If startdate is specified but enddate numdays is not, only the single day's data is returned.
enddate numdays - OPTIONAL - The end date when fetching historical data, or the number of days from startdate for which to return data. interval - OPTIONAL - The frequency of returned data; either 'DAILY' or 'WEEKLY'. interval can alternatively be specified as 1 or 7. Other numeric values are disallowed.
Notes. All parameters must be enclosed in quotation marks or be references to cells containing text, except when interval is specified as a number and when enddate numdays is specified as a number of days. Realtime results will be returned as a value within a single cell. Historical data, even for a single day, will be returned as an expanded array with column headers.
Some attributes may not yield results for all symbols. If any date parameters are specified, the request is considered historical and only the historical attributes are allowed. GOOGLEFINANCE is only available in English and does not support most international exchanges. Historical data cannot be downloaded or accessed via the Sheets API or Apps Script.
If you attempt to do so, you will see a #N/A error in place of the values in the corresponding cells of your spreadsheet. Information is provided 'as is' and solely for informational purposes, not for trading purposes or advice.
Dates passed into GOOGLEFINANCE are treated as noon UTC time. Exchanges that close before that time may be shifted by a day. Examples Note: Each example is in its own tab. General usage Retrieves market information from Google Finance. Common attributes Historical market data Retrieves historical market information based on the specified dates from Google Finance. Mutual funds Common attributes for mutual funds.
Currency exchange trends Creates a chart inside a cell to display the currency exchange trend during the last 30 days, using the retrieving result returns by GoogleFinance.