Dow 30 Stocks - Latest Financial Data in Excel

Get the latest financial data for the stocks in the Dow Jones Industrial Average. After clicking a button, Excel connects to the Internet and downloads the latest data.

The DIJA is most frequently quote market index in the world. It’s a key barometer of the strength of US equities, and reflects the value of the largest, most stable companies in the US, across many industries.

The 30 constituents include luminaries such as Apple Inc, Microsoft and Exxon Mobil (the three largest by market capitalization).

This spreadsheet gives you the financial lowdown on these blue-chip stalwarts. It’ll give you up-to-date information, including the EPS, P/E ratio, book value, last trade price, trading volume and more. In fact, you’ll get over 80 items of data.

Simple pick the data you want from one of ten drop-down menus, and click a button to kick-start some clever VBA.

The tool is simple to use – you simply click a single button to download the latest financial data. Use the information for stock screening, investment research and more

The spreadsheet is powered by VBA. Here’s part of the code – the rest is found in the VBA editor in Excel (it’s unlocked).

Sub GetData()
 
    Dim QuerySheet As Worksheet
    Dim tagSheet As Worksheet
    Dim workingSheet As Worksheet
    Dim qurl As String
    Dim i As Integer, j As Integer, k As Integer, tagNum As Integer
    Dim lastDataRow As Integer, lastQueryRow As Integer
    Dim nQuery As Name
    Dim startTime As Variant
 
    startTime = Now()
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationAutomatic
 
    Set QuerySheet = Sheets("Market Data")
    Set tagSheet = Sheets("Tags")
    Set workingSheet = Sheets("Working")
 
    QuerySheet.Range("B" & startRow & ":J" & 10000).Clear
 
    lastQueryRow = ActiveSheet.Cells(10000, "A").End(xlUp).Row
 
    'delete existing results
    QuerySheet.Range("C" & startRow & ":L10000").Clear
 
    j = startRow
    k = 15 ' number of tickers to download each time
 
    While j < lastQueryRow
 
        i = j
 
        qurl = "http://download.finance.yahoo.com/d/quotes.csv?s="
 
        While QuerySheet.Cells(i, 1) <> "" And i < j + k
            qurl = qurl + "+" + QuerySheet.Cells(i, 1)
            i = i + 1
        Wend
 
        For tagNum = 1 To 10
            workingSheet.UsedRange.Clear
QueryQuote:
            With workingSheet.QueryTables.Add(Connection:="URL;" & _
qurl & "&f=" & tagSheet.Cells(3, 6 + tagNum - 1), _
Destination:=workingSheet.Cells(1, 1))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
            workingSheet.Range("A1:a" & k).Copy _
            Destination:=QuerySheet.Range(Cells(j, tagNum + 2), _
Cells(j + k, tagNum + 2))
 
        Next tagNum
 
        j = j + k
 
        'Wait 1 second between calls so we don't overload Yahoo Finance
        Application.Wait (Now + TimeValue("00:00:01"))
 
    Wend
 
    deleteConnections
 
    With ThisWorkbook
        For Each nQuery In Names
            If IsNumeric(Right(nQuery.Name, 1)) Then
                nQuery.Delete
            End If
        Next nQuery
    End With
 
    Call OutlineResults(QuerySheet, lastQueryRow)
    Columns("B:L").HorizontalAlignment = xlLeft
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
 
    QuerySheet.Range("timeTaken").Value = DateDiff("s", startTime, Now())
 
End Sub

Sub GetData() Dim QuerySheet As Worksheet Dim tagSheet As Worksheet Dim workingSheet As Worksheet Dim qurl As String Dim i As Integer, j As Integer, k As Integer, tagNum As Integer Dim lastDataRow As Integer, lastQueryRow As Integer Dim nQuery As Name Dim startTime As Variant startTime = Now() Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationAutomatic Set QuerySheet = Sheets("Market Data") Set tagSheet = Sheets("Tags") Set workingSheet = Sheets("Working") QuerySheet.Range("B" & startRow & ":J" & 10000).Clear lastQueryRow = ActiveSheet.Cells(10000, "A").End(xlUp).Row 'delete existing results QuerySheet.Range("C" & startRow & ":L10000").Clear j = startRow k = 15 ' number of tickers to download each time While j < lastQueryRow i = j qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" While QuerySheet.Cells(i, 1) <> "" And i < j + k qurl = qurl + "+" + QuerySheet.Cells(i, 1) i = i + 1 Wend For tagNum = 1 To 10 workingSheet.UsedRange.Clear QueryQuote: With workingSheet.QueryTables.Add(Connection:="URL;" & _ qurl & "&f=" & tagSheet.Cells(3, 6 + tagNum - 1), _ Destination:=workingSheet.Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With workingSheet.Range("A1:a" & k).Copy _ Destination:=QuerySheet.Range(Cells(j, tagNum + 2), _ Cells(j + k, tagNum + 2)) Next tagNum j = j + k 'Wait 1 second between calls so we don't overload Yahoo Finance Application.Wait (Now + TimeValue("00:00:01")) Wend deleteConnections With ThisWorkbook For Each nQuery In Names If IsNumeric(Right(nQuery.Name, 1)) Then nQuery.Delete End If Next nQuery End With Call OutlineResults(QuerySheet, lastQueryRow) Columns("B:L").HorizontalAlignment = xlLeft Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic QuerySheet.Range("timeTaken").Value = DateDiff("s", startTime, Now()) End Sub

Feel free to learn, adapt and play with the code.

You can use the data for quantitative research using all of Excel’s calculation tools. You could, for example,

calculate the fair value of a company
estimate the Graham Number
find the best income stocks by sorting the companies in order of increasing dividend
estimate if a stock is good value by comparing the book value to the market value

Feel free to leave your comments

Excel Spreadsheet to Get Data for the Companies in the Dow Jones Industrial Average