That can easily be done with the DLL MetaLib from
http://www.trading-tools.com

You only have to use following code in your Excel sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Column = 1 Then
Dim iquotes As New MLInternetQuotes
iquotes.DownloadQuotes Target.Cells(Target.Row, 2), US
Target.Cells(Target.Row, 3) = iquotes.dClose
End If
End Sub