Hjælp til makro - henter ikke rigtige tal fra Yahoo Finance
Hej alle,Jeg har behov for assistance, da jeg har siddet med nedenstånde problem i laaaang tid.
Jeg benytter denne makro:
________________________________________________________________
Sub Financial_data_download()
Sheets("Selection").Select
Columns("A:A").Select
Selection.ClearContents
Range("A1").Select
Sheets("Stocks").Select
Worksheets("stocks").Activate
Range("A1:A3").Select
Selection.ClearContents
Range("A1").Select
Worksheets("stocks").Activate
Range("A1").Select
Selection.Value = Now
Application.ScreenUpdating = False
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Selection").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Dim conString As String
Dim conName As String
Dim x As String
Dim i As Integer
Sheets("Selection").Select
numrows = Range("A1", Range("a1").End(xlDown)).Rows.Count
Range("a1").Select
For i = 1 To numrows
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = Sheets("selection").Cells(i, 1).Value
conString = "URL;http://finance.yahoo.com/q/is?s=" & x & "+Income+Statement&annual"
conName = "ks?s=" & x & "+Income+Statement&annual"
Range("$A$1").Value = "Financial income statement"
With ActiveSheet.QueryTables.Add(Connection:=conString, Destination:=Range("$B$1"))
.Name = conName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """yfncsubtit"",8,10,11,13,15,17,19,21,23"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
conString = "URL;http://finance.yahoo.com/q/is?s=" & x & "+Balance+Sheet&annual"
conName = "ks?s=" & x & "+Balance+Sheet&annual"
conString = "URL;http://finance.yahoo.com/q/bs?s=" & x & "+Balance+Sheet&annual"
conName = "ks?s=" & x & "+Balance+Sheet&annual"
Range("$A$50").Value = "Balance sheet"
With ActiveSheet.QueryTables.Add(Connection:=conString, Destination:=Range("$B$50"))
.Name = conName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """yfncsubtit"",8,10,11,13,15,17,19,21,23"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End With
Next
Worksheets("stocks").Activate
Range("A3").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Samlet tid: "",RC[2],"" time(r)"","", "",R[-1]C[2],"" minut(ter)"","" og "",R[-2]C[2],"" sekund(er)"")"
Range("A1").Select
Worksheets("stocks").Activate
Range("a2").Select
Selection.Value = Now
Application.ScreenUpdating = True
Range("a1").Select
End Sub
_____________________________________________________________
Når jeg henter data, så får jeg nogle tal, som ikke stemmer overens med hvad der står på Yahoo Finance.
F.eks. hvis jeg henter fra Microsoft, står der ingen omsætning, men kigger man på yahoo finance, har microsoft self. haft masser af omsætning. Ligeledes henter den på kvartaler - jeg vil gerne den hentede på årsbasis.
Nogle som kan hjælpe?
