Kurs Aktualisierung

Option Explicit

Public Function getData(ISIN As String, stockType As String, Optional returnValue As Integer)
Dim wstemp As Worksheet
Dim URL As String

  On Error GoTo errHandler

  Select Case LCase(stockType)
    ' Anleihe
    Case "bond"
      URL = "https://wertpapiere.ing-diba.de/DE/Showpage.aspx?pageID=41&ISIN="
    ' Aktie
    Case "stock", "share"
      URL = "https://wertpapiere.ing-diba.de/DE/Showpage.aspx?pageID=23&ISIN="
    ' ETF
    Case "etf"
      URL = "https://wertpapiere.ing-diba.de/DE/Showpage.aspx?pageID=30&ISIN="
    ' wtf
    Case Else
      getData = "ERROR - wrong stock type"
      Exit Function
  End Select

  Set wstemp = Worksheets("temp")

  wstemp.Range("A1:IV65535").ClearContents

  With wstemp.QueryTables.Add(Connection:="URL;" & URL & Trim(ISIN) & "", Destination:=wstemp.Range("A1"))
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = False
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .WebSelectionType = xlAllTables
      .WebFormatting = xlWebFormattingNone
      .WebPreFormattedTextToColumns = True
      .WebConsecutiveDelimitersAsOne = True
      .WebSingleBlockTextImport = False
      .WebDisableDateRecognition = False
      .WebDisableRedirections = False
      .Refresh BackgroundQuery:=False
  End With
  DoEvents

  ' error handling
  If wstemp.Range("A1").Value = "" Or IsNumeric(wstemp.Range("A1").Value) = False Then
    getData = "ERROR - return Value was zero"
    Exit Function
  Else
    If returnValue <> 0 Then
      getData = CDbl(wstemp.Range("B16").Value)
    Else
      getData = CDbl(wstemp.Range("A1").Value)
    End If
  End If

  wstemp.Range("A1:IV65535").ClearContents
  wstemp.Range("A1:IV65535").QueryTable.Delete

Exit Function

errHandler:
  getData = "ERROR"

End Function