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
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