historical stock close price data

[econberkeley]

It is taking me a long time to get the stock close prices one by one in Yahoo finance for a set of stocks. Is there a free website that lets you download the historical prices for all the US stocks for a certain day?

[hodedofome]

Not aware of a free one. I'm sure an Excel junkie could figure out how to download that data from Yahoo or FinViz. You make look to see if Google Finance has a formula for getting the price of a stock in the past. https://support.google.com/docs/answer/3093281

[skyrefuge]

Here's a link describing how to do (mostly) what you want within Google Sheets (I'm just not sure if the API will allow you to get *all* US stocks).

https://productforums.google.com/forum/#!topic/docs/RStGQ3JxzE8

[econberkeley]

Google sheets worked great!! Amazing!!!! Thank you both!!

[forummm]

What's the project? Sounds interesting.

[econberkeley]

I am testing different investment strategies to buy set of stocks. It is very hard to monitor the average return of the portfolio unless you use tools like google sheets. I saved so much time using google sheets.

[Doulos]

It sounds like you are trying to handle your own mutual fund?
Tailored to your specific tastes?

[bdbrooks]

I have recently done something similar. I use a database from Quandl.com where I can get a batch download of fundamental data that I can manipulate with excel and access. With Excel VBA I pull in recent stock data from Yahoo's API (I select 40 stocks based off of 3-year price to earnings ratio AND momentum factors and invest with FOLIO where transactions are free). This is a little more complex, but it allows for better control than using Google Sheets.

I have pasted below a section where I pull in the past price information. If you don't have any idea how to code, then you will likely want to ignore this. I'm just posting it in case anyone finds it interesting.


j is the rows that have ticker symbols in them
MinCap is the Minimum Market Cap that I allow
Note that I am operating with Excel 2013 and I believe the Split function needs to be adjusted for prior versions
Note that I am pasting in the momentum (running it last day of the month): Price 2 month ago/ Price 12 months ago - 1. We ignore price fluctuations from the last month. There is significant research that shows that momentum strategies work best by ignoring the price fluctuations over the prior 2 months.


For j = 2 To Last + 1
        If W.Cells(j, 10).value >= MinCap Then
            On Error Resume Next
            strTicker = W.Cells(j, 1)
            strURL = "http://ichart.finance.yahoo.com/table.csv?s=" & strTicker & _
            "&a=" & Month(dtPrevDate) - 1 & _
            "&b=" & Day(dtPrevDate) & _
            "&c=" & Year(dtPrevDate) & _
            "&d=" & Month(dtDate) - 1 & _
            "&e=" & Day(dtDate) & _
            "&f=" & Year(dtDate) & _
            "&g=m&ignore=.csv"
 
            Http.Open "GET", strURL, False
            Http.Send
            strCSV = Http.ResponseText
 
            strRows() = Split(strCSV, Chr(10))
            strColumns = Split(strRows(2), ",")
            dbEnd = strColumns(4)
 
            strColumns = Split(strRows(12), ",")
            dbStart = strColumns(4)
   
            If Err.Number <> 0 Then
                dbEnd = 0
                dbStart = 1
            End If
 
            W.Cells(j, 12).value = (dbEnd / dbStart) - 1
   
       
            Set Http = Nothing
        Else
            W.Cells(j, 12).value = ""
        End If
       
        Counter = Counter + 1
        MainBar.NextAction "Downloading Data " & Counter, True
   
    Next j

Consider this for informational purposes only. Always do your own research. Momentum strategies usually need to be reset either monthly for quarterly to be effective.

[CB]

(1) Download R (the RStudio package has a very nice IDE for R with lots of things set up for you).
(2) Install the "quantmod" package
(3) Go nuts with getSymbols()

[hodedofome]

I have recently done something similar. I use a database from Quandl.com where I can get a batch download of fundamental data that I can manipulate with excel and access. With Excel VBA I pull in recent stock data from Yahoo's API (I select 40 stocks based off of 3-year price to earnings ratio AND momentum factors and invest with FOLIO where transactions are free). This is a little more complex, but it allows for better control than using Google Sheets.

I have pasted below a section where I pull in the past price information. If you don't have any idea how to code, then you will likely want to ignore this. I'm just posting it in case anyone finds it interesting.


j is the rows that have ticker symbols in them
MinCap is the Minimum Market Cap that I allow
Note that I am operating with Excel 2013 and I believe the Split function needs to be adjusted for prior versions
Note that I am pasting in the momentum (running it last day of the month): Price 2 month ago/ Price 12 months ago - 1. We ignore price fluctuations from the last month. There is significant research that shows that momentum strategies work best by ignoring the price fluctuations over the prior 2 months.


For j = 2 To Last + 1
        If W.Cells(j, 10).value >= MinCap Then
            On Error Resume Next
            strTicker = W.Cells(j, 1)
            strURL = "http://ichart.finance.yahoo.com/table.csv?s=" & strTicker & _
            "&a=" & Month(dtPrevDate) - 1 & _
            "&b=" & Day(dtPrevDate) & _
            "&c=" & Year(dtPrevDate) & _
            "&d=" & Month(dtDate) - 1 & _
            "&e=" & Day(dtDate) & _
            "&f=" & Year(dtDate) & _
            "&g=m&ignore=.csv"
 
            Http.Open "GET", strURL, False
            Http.Send
            strCSV = Http.ResponseText
 
            strRows() = Split(strCSV, Chr(10))
            strColumns = Split(strRows(2), ",")
            dbEnd = strColumns(4)
 
            strColumns = Split(strRows(12), ",")
            dbStart = strColumns(4)
   
            If Err.Number <> 0 Then
                dbEnd = 0
                dbStart = 1
            End If
 
            W.Cells(j, 12).value = (dbEnd / dbStart) - 1
   
       
            Set Http = Nothing
        Else
            W.Cells(j, 12).value = ""
        End If
       
        Counter = Counter + 1
        MainBar.NextAction "Downloading Data " & Counter, True
   
    Next j

Consider this for informational purposes only. Always do your own research. Momentum strategies usually need to be reset either monthly for quarterly to be effective.

Pretty cool, thanks for this.

I track a similar portfolio with data from finviz where I look at last 12 months momentum and stocks with positive earnings. Pretty promising so far.

[691175002]

Be careful when backtesting earnings because some data sources make it seem available before it really is.  For example, if your database lists earnings as of financial statement date (ex: Q4 at Dec31) instead of when they were reported (several months later) you are backtesting using "future earnings" which is of course not possible in real life.

[bdbrooks]

Be careful when backtesting earnings because some data sources make it seem available before it really is.  For example, if your database lists earnings as of financial statement date (ex: Q4 at Dec31) instead of when they were reported (several months later) you are backtesting using "future earnings" which is of course not possible in real life.

Agreed. That is why I use a paid subscription for fundamental data and only trust Yahoo's API for the pricing information.