The queries for Dow Jones and S&P 500 will result in error:
While a query for Apple will succeed:
This is not something you will want to hear, but most free websites have licensing issues and display limited or no download links for S&P and DJI. I recommend you use quantdl or stooq instead.
Here are some stooq download links:
DJI:
https://stooq.com/q/d/l/?s=^dji&i=d
S&P:
https://stooq.com/q/d/l/?s=^spx&i=d
You do need to clean up the data before you use it though. S&P data goes all the way back to 1789 and DJI goes back to 1896, so I don't even know if it is possible... I wouldn't trust any data before 1960 on any website.
The wsj website lets you download csv data for DJI. If you play around a little bit, you can figure out the url query to kick off a download. The, you can use Powershell to make the download programmatic (note: this script also accesses stock quotes from yahoo finance using their currently available csv download method) (Note: the codes for the yahoo query are documented here: http://www.canbike.org/information-technology/yahoo-finance-url-download-to-a-csv-file.html)
#script follows
cls
Function Get-LastFriday
{
$djiDate = [DateTime]::Now
while ($djiDate.DayOfWeek -ne "Friday")
{
$djiDate= [DateTime]::Now.AddDays(-1)
}
return $djiDate
}
#prototype: quotes.wsj.com/index/DJIA/historical-prices/download?MOD_VIEW=page&num_rows=1&range_days=1&startDate=08/20/2016&endDate=08/20/2016
$djiDate = Get-LastFriday
$startDate = $endDate = ([String]$djiDate.Month) + "/" + ([String]$djiDate.Day) + "/" + ([String]$djiDate.Year)
$uri = "quotes.wsj.com/index/DJIA/historical-prices/download?MOD_VIEW=page&num_rows=1&range_"+$startDate+"&endDate="+$endDate
Invoke-WebRequest -Uri $uri -OutFile "C:\Users\xxxx\Desktop\dji.csv"
Import-Csv -Header Date, Open, High, Low, Close -LiteralPath "C:\Users\bill\Desktop\dji.csv" | Out-GridView
Invoke-WebRequest -Uri "http://download.finance.yahoo.com/d/quotes.csv?s=^DJI,VWIAX,VMMXX,VWELX,DODGX&f=nsl1d1t1p&e=.csv" -OutFile "C:\Users\xxxx\Desktop\q.csv"
Import-Csv -Header Name, Symbol, 'Last Trade', 'Trade Date', 'Last Trade Time', p -LiteralPath "C:\Users\bill\Desktop\q.csv" | Out-GridView
Symbol for DJIA is ".DJI" (without the quotes but with the leading period) https://www.google.com/finance/historical?q=.DJI&startdate=Jun+01%2C+2017&output=txt
Symbol for NASDAQ is ".IXIC" (without the quotes but with the leading period) https://www.google.com/finance/historical?q=.IXIC&startdate=Jun+01%2C+2017&output=txt
Symbol for S&P500 is ".INX" (without the quotes but with the leading period) https://www.google.com/finance/historical?q=.INX&startdate=Jun+01%2C+2017&output=txt
if you reference these actual index name it works
INDEXSP:.INX INDEXDJX:.DJI
Pandas DataReader (separate library to Pandas) works with Yahoo finance (not google finance).
Installation: pip install pandas_datareader
Code: I just run the below to get the SPX data between 2010 and 2015 and it works:
This link here has an up-to-date list of supported data providers (Yahoo is not there right now but the code still works as shown above & returns good data).
It appears that Google doesn't support downloading data for the Dow Jones Industrial Average and quite possibly the S&P 500 Index too. See the end of this thread: http://www.mrexcel.com/forum/excel-questions/471110-web-query-obtain-historical-stock-data-using-google-finance.html.
You can use the code provided top right by Google when you search for Dow Jones Today (or any other stock market), so =googlefinance("INDEXDJX:.DJI"). Not sure where the code comes from but it works fine.
