yahoo!-finance (or other) historic data in excel

Question

The way I see it, there are two possibilities to get yahoo!-Finance data into excel. The first is for real-time data, the second for historic data.

I am in need of historic data. My current VBA-code is as follows:

firstcolumn = 2
lastcolumn = 6


For n = firstcolumn To lastcolumn

Ticker = Worksheets(1).Cells(3, n).Value

  ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
      With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;http://ichart.finance.yahoo.com/table.csv?s=" & Ticker & "&d=" & Month(Date) & "&e=" & Day(Date) & "&f=" & Year(Date) & "&g=d&" _
        & "a=" & Month(Date) & "&b=" & Day(Date) & "&c=" & Year(Date) - 1 & "&ignore=.csv" _
        , Destination:=Range("$A$1"))
        .Name = "table.csv?s=BMW.DE&d=6&e=31&f=2012&g=d&a=0&b=1&c=2003&ignore="
        .FieldNames = True
        .RowNumbers = True
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(5, 1, 1, 1, 1, 1, 1)
        .TextFileDecimalSeparator = "."
        .TextFileThousandsSeparator = ","
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

    End With
ActiveSheet.Name = Ticker

MsgBox "status ende"

ActiveWorkbook.Connections("table.csv?s=" & Ticker & "&d=" & Month(Date) & "&e=" & Day(Date) & "&f=" & Year(Date) & "&g=d&a=" & Month(Date) & "&b=" _
   & "" & Day(Date) & "&c=" & Year(Date) - 1 & "&ignore=").Delete
ActiveSheet.QueryTables.Item(ActiveSheet.QueryTables.Count).Delete
ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, 7).End(xlDown)), , xlYes).Name = Ticker

'MsgBox "The data for " & Ticker & " were downloaded to a new sheet."
Next n

Exit Sub
ERR:
MsgBox "Error. Please check."

Which goes through a list of stock tickers and creates a new sheet with the stock's ticker as name, and the historical data downloaded as follows:

It is then up to me to extract the relevant columns, date and close price, and copy it to where I need them in the worksheet.

I found the VBA code I use online, but I could not determine how to make it such that only the data and the close price column appear. As far as I can see, the code does not contain any query on "open", "high", "low", "close" that I could exclude to receive only the data I want. I also do not see what the line .Name = "table.csv?s=BMW.DE&d=6&e=31&f=2012&g=d&a=0&b=1&c=2003&ignore=" could be for, as those dates are not reflected in my data. Last, even though I ask for daily data from today until one year back, the data goes back until 3.1.2000.

In sum, this unflexible way is the only one I found to gain the necessary data. What I would like to, however, is something that looks of the following form:

i.e. with the difference that I can adjust which data I need (instead of all of open, high, low, ...), and where to insert it in the existing sheet (instead of $A$1 in a new sheet).

Alternatively, I would use any other database, if suggested. The version of Excel is 2013. I looked at the Webservice function, but that also only seems to fetch current data, not historic data.

Answer 1

I found a way to use the Webservice function (and do it the hard way).

In the table, column A contains date values, and C1 contains a ticker. The cell C2 contains:

=WEBSERVICE("http://ichart.finance.yahoo.com/table.csv?s="&C$1&"&a="&MONTH($A2)-1&"&b="&DAY($A2)&"&c="&YEAR($A2)&"&d="&MONTH($A2)-1&"&e="&DAY($A2)&"&f="&YEAR($A2)&"&g=d&ignore=.csv")

This imports the data (as visualized in the question), but in .csv-format, for a single day. With the given cell references $A2 for date and C$1 for ticker, this formula can be dragged to the right for more companies, and downwards for more dates.

The content of cell C2 is then:

"Date,Open,High,Low,Close,Volume,Adj Close 2016-01-07,153.15,154.95,151.55,153.75,2454400,138.63 "

Next, cell C8 contains the position of the tenth comma, because the close price begins after the tenth comma in the field C2:

=FIND("X";SUBSTITUTE(C2;",";"X";10))

Next, cell C9 is the remaining string after the 10th comma.

=RIGHT(C2;LEN(C2)-C8)

Next, C10 is everything up until the first comma in C9:

=LEFT(C9;FIND(",";C9)-1)

Next, C11 is C10, converted to decimal with a given decimal separator:

=NUMBERVALUE(C10;".")

And finally, to save room, we can combine all these functions in that order and put it into C2 to get the same. It will look like this (C13):

=NUMBERVALUE(LEFT(RIGHT(WEBSERVICE("http://ichart.finance.yahoo.com/table.csv?s="&C$1&"&a="&MONTH($A2)-1&"&b="&DAY($A2)&"&c="&YEAR($A2)&"&d="&MONTH($A2)-1&"&e="&DAY($A2)&"&f="&YEAR($A2)&"&g=d&ignore=.csv");LEN(WEBSERVICE("http://ichart.finance.yahoo.com/table.csv?s="&C$1&"&a="&MONTH($A2)-1&"&b="&DAY($A2)&"&c="&YEAR($A2)&"&d="&MONTH($A2)-1&"&e="&DAY($A2)&"&f="&YEAR($A2)&"&g=d&ignore=.csv"))-FIND("X";SUBSTITUTE(C2;",";"X";10)));FIND(",";RIGHT(WEBSERVICE("http://ichart.finance.yahoo.com/table.csv?s="&C$1&"&a="&MONTH($A2)-1&"&b="&DAY($A2)&"&c="&YEAR($A2)&"&d="&MONTH($A2)-1&"&e="&DAY($A2)&"&f="&YEAR($A2)&"&g=d&ignore=.csv");LEN(WEBSERVICE("http://ichart.finance.yahoo.com/table.csv?s="&C$1&"&a="&MONTH($A2)-1&"&b="&DAY($A2)&"&c="&YEAR($A2)&"&d="&MONTH($A2)-1&"&e="&DAY($A2)&"&f="&YEAR($A2)&"&g=d&ignore=.csv"))-FIND("X";SUBSTITUTE(WEBSERVICE("http://ichart.finance.yahoo.com/table.csv?s="&C$1&"&a="&MONTH($A2)-1&"&b="&DAY($A2)&"&c="&YEAR($A2)&"&d="&MONTH($A2)-1&"&e="&DAY($A2)&"&f="&YEAR($A2)&"&g=d&ignore=.csv");",";"X";10))))-1);".")

And this can now equally be dragged to the right and downward to get data for more firms on more days.

However, if there is a more elegant solution, I will be very happy to see it!