This Excel spreadsheet downloads stock quotes for 6855 companies from Finviz.com using VBA. The stock quotes includes over 65 pieces of financial data, including PE ratios, market capitalization, EPS, moving averages, earnings dates, average true range, and much more.
The VBA can be viewed or edited, and provides a basis for more sophisticated spreadsheets, including stock screeners and stock comparison spreadsheets. Similar techniques have been used to download Yahoo stock quotes and forex rates.
This is the VBA used in the spreadsheet.
Sub GetFinvizData() Dim str As String 'Delete existing data Sheets("Data").Activate 'Name of sheet the data will be downloaded into. Change as required. Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents 'Download stock quotes. Be patient - takes a few seconds. str = "http://finviz.com/export.ashx?v=151&c=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68" QueryQuote: With Sheets("Data").QueryTables.Add(Connection:="URL;" & str, Destination:=Sheets("Data").Range("a1")) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With Sheets("Data").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Data").Range("a1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2) Sheets("Data").Columns("A:B").ColumnWidth = 12 Range("A1").Select End Sub
The stock quotes includes these items: ticker, company name, sector, industry, country, market cap, P/E, Forward P/E, PEG, P/S, P/B, P/Cash, P, Free Cash Flow, dividend yield, payout ratio, EPS (ttm), EPS growth this year, EPS growth next year, EPS growth past five years, sales growth past five years, EPS growth quarter over quarter, sales growth quarter over quarter, shares outstanding, shares float, insider ownership, insider transactions, institutional ownership, institutional transactions, float short, short ratio, return on assets, return on equity, return on investment, current ratio, quick ratio, LT debt/equity, total debt/equity, gross margin, operating margin, profit margin, performance (week), performance (month), performance (quarter), performance (half year), performance (year), beta, average true range, volatility (week), volatility (month), 20-day simple moving average, 50-day simple moving average, 200-day simple moving average, 50-day high, 50-day low, 52-week high, 52-week low, relative strength index (14), change from open, gap, analyst recommendation, average volume, relative volume, price, change, volume, earnings data.
Run the macro GetFinviz data to retrieve the stock quotes. The spreadsheet downloads 3.5MB of data, so the macro may take a few seconds (30 seconds on my Internet connection).
All the data in the spreadsheet can be saved and manipulated to develop your own stock screening spreadsheets. The stock quotes only need to be updated once a day (or whenever you require updated data).