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

An Excel spreadsheet that downloads financial data for the companies in the Dow Jones Industrial Average

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

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,

Feel free to leave your comments

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


2 thoughts on “Dow 30 Stocks – Latest Financial Data in Excel

  1. Receiving the following error when executing “Dow 30 Stocks- Latest Financial Data”. Any ideas?
    Run-time error’1004″
    Method “refresh” of object”_QueryTable” Failed

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 12 + 11 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)