Thanks for the quick reply. If I double click on the cell it comes up with the formula and then pressing enter returns it to the original figures.
This is the VBA module I've been working on. The comments are for me - so that I can remember how it works with an array! I'm new to this and I've copied bits of it from elsewhere so, if you could also explain the 3 lines after set.http I'd be very grateful! The function needs to be called with a stockmarket ticker (eg goog or msft) and I then enter the formula into an array of 21 columns.
Function DK(ticker As String)
'Needs to be called as an array function
'Select the required number of columns in the target row
'call the DK function in the formula bar and hold down SHIFT+CTRL as pressing ENTER
Dim url As String
Dim http As Object
Dim csv As String
Dim temp() As String
'downloads csv file with ticker, name (n) and last price (l1)
url = "http://download.finance.yahoo.com/d/quotes.csv?s=" & ticker & "&f=sna2c1hgm3l1opm4vkjabf6j1s1j2t7&ignore=.csv"
On Error Resume Next
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", url, False
csv = http.responseText
temp = Split(csv, ",")
temp(0) = Mid(temp(0), 2, Len(temp(0)) - 2)
temp(1) = Mid(temp(1), 2, Len(temp(1)) - 2)
DK = temp
Set http = Nothing