financials from morning star

Here is a UDf that I use. I found it about a year ago. I wish I could give credit to whomever wrote it, but I don't have that info. You can list stocks and it will give you multiple quotes.

Function StockQuote(strTicker As String, Optional dtDate As Variant)
' Date is optional - if omitted, use today. If value is not a date, throw error.
If IsMissing(dtDate) Then
dtDate = Date
Else
If Not (IsDate(dtDate)) Then
StockQuote = CVErr(xlErrNum)
End If
End If

Dim dtPrevDate As Date
Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
Dim dbClose As Double

dtPrevDate = dtDate - 7

' Compile the request URL with start date and end date
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=d&ignore=.csv"

' Debug.Print strURL

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strCSV = http.responseText

' Debug.Print strCSV

' The most recent information is in row 2, just below the table headings.
' The price close is the 5th entry
strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows
strColumns = Split(strRows(1), ",") ' split the relevant row into columns. 1 means 2nd row, starting at index 0
dbClose = strColumns(4) ' 4 means: 5th position, starting at index 0

' Debug.Print vbLf
' Debug.Print strRows(1)
' Debug.Print "dbClose: " & dbClose

'ActiveCell.Value = ActiveCell.Value
'ActiveCell.NumberFormat = "Currency"

StockQuote = dbClose

Set http = Nothing
'Call copypastespecial
End Function

Sure, here is a sample worksheet. I have used three different dates to show you how to set up a sheet for multiple stocks and dates.

Data Range

	A	B	C	D	E
1			Price
2	Stock Name	Symbol	1/15/2015	2/25/2016	Latest
3	Altria	MO	52.470001	62.380001	66.53
4	C&S	CSRSX	81.93	67.139999	76.61
5	Baxter	BAX	71.479999	39.84	48.06
6	HarleyD	HOG	62.639999	43.32	53.49
7	Heartland	HRTVX	41.650002	32.740002	37.54
8	Intel	INTC	36.189999	29.620001	34.98
9	Merger	MERFX	15.52	15.24	15.55
10	Mondelez	MDLZ	36.540001	41.580002	43.27
11	NXPI	NXPI	76.610001	70.900002	85.74
12	PM Int'l	PM	82.82	92.449997	98.93
13	Pinnacle West	PNW	70.599998	71.209999	77.17
14	Prudential	Pru	79.779999	66.07	76.03
15	S&P iShares	IVV	200.42999	196.5	219.4
16	Shire PLC	SHPG	211.03	162.77	200.87
17	Vanguard Tech	VGT	100.54	101.62	117.09
18	VIS	VIS	101.96	98.419998	110.98
19

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

this will now be a function that you can access in the same manner that you would for any other Excel function. It is called stockquote. So in the cell you want the data, type =Stockquote(Symbol, Date of Quote). If no date is included in the formula, then it returns the latest value.