CODEWITHSUNDEEP
excelvbayahoo-finance
Marie. P.
Marie. P.

Reputation: 275

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

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:

enter image description here

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:

enter image description here

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.

Upvotes: 0

Views: 2478

Answers (2)

Yannis
Yannis

Reputation: 135

If your question is about getting live quotes and historic data from YF into Excel then there is a far simpler way than using vba, which by the way will most likely not work due to YF's shut down of their api service as of Nov 2017. You may use the almost free Deriscope addin that retrieves these data into Excel. Disclaimer: I am the developer of Deriscope.

Upvotes: 0

Marie. P.
Marie. P.

Reputation: 275

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

enter image description here

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!

Upvotes: 1

Related Questions