Value Investing Scanner — Diagramme

Den ersten Entwurf des Tools habe ich nun um die automatische Darstellung der wichtigsten Größen in Säulendiagrammen ergänzt. Hier erkläre ich meine Vorgehensweise und stelle diese Zwischenlösung zur Verfügung.

Die Vorüberlegungen bzw. ersten Schritte zu diesem kleinen Projekt können hier nachgelesen werden:
Value Investing Scanner — Planung
Value Investing Scanner — der erste Entwurf

Bisher hatte ich ein Tabellenblatt „Datenblatt“ als Vorlage für die Bewertung einer Aktie erstellt und dazu ein VBA-Makro „HolDieDaten“ programmiert, das alle benötigten Daten zu einer vorgegebenen Aktie (ISIN) aus dem Web in das Tabellenblatt einliest. Durch etliche Excel-Formeln auf dem Datenblatt werden sofort einige Kennzahlen berechnet, z.B. Dividendenrendite, KGV usw. Diese kann man im Kopfbereich ablesen.

Weiter unten waren bereits mit Hilfe von Formeln sechs Tabellen mit Zusammenfassungen verschiedener Kennzahlen über alle verfügbaren Jahre in der Vergangenheit erstellt worden. In der Erweiterung, um die es hier geht, wird zu jeder dieser Zusammenfassungen ein Säulendiagramm dargestellt. Damit sieht man auf einen Blick jeweils die Entwicklung über die Jahre. Im Titel dieser Diagramme wird, sofern das sinnvoll und möglich ist, gleich die durchschnittliche jährliche prozentuale Steigerung mit angegeben.

Die Umsetzung

Zu diesem Zweck habe ich eine function ErzeugeDiagramme geschrieben und innerhalb des Makros Sub HolDieDaten verwendet. Durch function erscheint das nicht in der Liste der Makros. Hier der Quelltext. Die Kommentare darin erklären ihn.

Function ErzeugeDiagramme(sh As Worksheet, letztesJahrSpalte As Integer)

   Dim diaFelder As Variant
   Dim rng As Range, X0 As Integer, Y0 As Integer
   Dim V As Variant, k As Integer
   Dim cho As ChartObject, ch As Chart
   Dim steigerung As Variant, prozent As String

   Const diaBreite = 360 'Breite eines Diagramms
   Const diaHoehe = 240 'Höhe eines Diagramms
   Const diaAbstand = 20 'Abstand der Diagramme zueinander

   'Einstellungen für Diagramme - jeweils durch # getrennt
   'Namen von Zellen durch @ getrennt: Zelle_mit_Jahr@darzustellende_Werte[@prozentuale_Steigerung]
   diaFelder = "DIVJahr@DIVBetrag@DIVSteigerungDurchschnitt#EPSJahr@EPSBetrag@EPSSteigerungDurchschnitt#" + _
   "ANTJahr@ANT#CFJahr@CFBetrag@CFSteigerungDurchschnitt#" + _
   "UMSJahr@UMSBetrag@UMSSteigerungDurchschnitt#EKJahr@EKBetrag@EKSteigerungDurchschnitt"
   diaFelder = Split(diaFelder, "#")

   'Ab wo werden die Diagramme gezeichnet?
   Set rng = sh.Range("EKSteigerungDurchschnitt")
   Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(rng.Row, rng.Column - 1))
   X0 = rng.Width + 10
   Y0 = rng.Height + 5

   For k = 0 To UBound(diaFelder)

      V = Split(diaFelder(k), "@")

      'Diagramm positionieren und vorbereiten
      Set cho = sh.ChartObjects.Add(X0 + (k Mod 2) * (diaBreite + diaAbstand), Y0 + (k - (k Mod 2)) / 2 * (diaHoehe + diaAbstand), diaBreite, diaHoehe)
      Set ch = cho.Chart
      ch.ChartType = xlColumnClustered

      'Werte und Überschrift
      Set rng = sh.Range(V(1))
      Call ch.SetSourceData(sh.Range(rng, sh.Cells(rng.Row, letztesJahrSpalte)))
      prozent = ""  'für durchschnittliche jährliche Steigerung
      If UBound(V) >= 2 Then
         steigerung = sh.Cells(sh.Range(V(2)).Row, letztesJahrSpalte).Value
         If IsNumeric(steigerung) Then
            prozent = " (d. St. " & Format(steigerung * 100, "#.0") & "%)"
         End If
      End If
      ch.HasTitle = True
      ch.ChartTitle.Text = CStr(sh.Cells(rng.Row, rng.Column - 1).Value) & prozent

      'Jahreszahlen an die X-Achse schreiben
      Set rng = sh.Range(V(0))
      ch.FullSeriesCollection(1).XValues = sh.Range(rng, sh.Cells(rng.Row, letztesJahrSpalte))

      'ohne Legende
      If ch.HasLegend Then
         ch.Legend.Delete
      End If

   Next

End Function

Im Makro „HolDieDaten“ wird diese Function aufgerufen, nachdem alle Daten im Tabellenblatt dargestellt sind:

Call ErzeugeDiagramme(sh, letztesJahrSpalte)

In sh steht dabei das entsprechende Worksheet, in letztesJahrSpalte steht die Nummer der letzten darzustellenden Spalte aus der Tabelle. Diese wird vorher innerhalb des Makros ermittelt und übergeben.

Zusätzlich habe ich eine function LeerBlatt geschrieben, die alle Angaben bis auf Aktienbezeichnung und ISIN aus dem Tabellenblatt wieder herauslöscht und die Diagramme entfernt. Sie bekommt als Argument das Datenblatt Worksheet sh

Function LeerBlatt(sh As Worksheet)

   Dim einfachFelder As Variant
   Dim mehrfachFelder As Variant
   Dim mehrfachFormeln As Variant

   Dim k As Integer, j As Integer
   Dim rng As Range, zeile As Integer, spalte As Integer
   Dim letzteSpalte As Integer

   Dim cho As ChartObject

   einfachFelder = "Jahresende#letztesJahr#AngabenIn#Datum#Kurs"
   einfachFelder = Split(einfachFelder, "#")

   mehrfachFelder = "Jahr#Bilanzsumme#Eigenkapital#" + _
   "Umsatz#EBIT#Ueberschuss#UeberschussQ1#UeberschussQ2#UeberschussQ3#UeberschussQ4#" + _
   "Anzahl#EPS#Dividende#UPS#BPS#CPS#" + _
   "KGV#KUV#KBV#KCV"
   mehrfachFelder = Split(mehrfachFelder, "#")

   mehrfachFormeln = Split(FORMELN_MEHRFACH, "#")

   'Löschen der Inhalte
   For k = 0 To UBound(einfachFelder)
      sh.Range(einfachFelder(k)).ClearContents
   Next
   For k = 0 To UBound(mehrfachFelder)
      Set rng = sh.Range(mehrfachFelder(k))
      rng.ClearComments
      zeile = rng.Row
      spalte = rng.Column
      If k = 0 Then
         Do Until CStr(sh.Cells(zeile, spalte).Value) = ""
            sh.Cells(zeile, spalte).ClearContents
            spalte = spalte + 1
         Loop
         letzteSpalte = spalte - 1
      Else
         For j = spalte To letzteSpalte
            sh.Cells(zeile, j).ClearContents
         Next
      End If
   Next
   For k = 0 To UBound(mehrfachFormeln)
      Set rng = sh.Range(mehrfachFormeln(k))
      zeile = rng.Row
      spalte = rng.Column
      For j = spalte + 1 To letzteSpalte
         sh.Cells(zeile, j).ClearContents
      Next
   Next

   'Diagramme entfernen
   For Each cho In sh.ChartObjects
      cho.Delete
   Next

End Function

FORMELN_MEHRFACH habe ich als Konstante außerhalb von Sub und Function ausgelagert, weil sie doppelt verwendet wird.

Der Aufruf der function LeerBlatt passiert innerhalb des Makros „HolDieDaten“ gleich nach der Zuweisung des Datenblattes:

Set sh = Sheets("Datenblatt")
Call LeerBlatt(sh)

Zusätzlich habe ich für mich noch ein einfaches Hilfsmakro geschrieben, mit dem ich das Blatt einfach so leeren kann, ohne neue Daten einzulesen:

Sub LeerDieDaten()

   Dim sh As Worksheet

   Set sh = Sheets("Datenblatt")
   Call LeerBlatt(sh)

End Sub
Download: Entwurf
Entwurf: scanner-entwurf.xslm 48 KB

Ausblick

Im nächsten Schritt will ich bereits Code und Daten voneinander trennen. Die Datenblätter sollen in einzelnen Dateien übersichtlich abgelegt werden. In der Datei mit den Makros (Maschine) sollen sich nur die Vorlage, das Hilfsblatt für die Web-Abfragen sowie ein Blatt mit allgemeinen Einstellungen (Pfade usw.) befinden. Die dritte „Komponente“ wird eine Datei mit einer Liste der zu scannenden Aktien bilden.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.