Herbers Excel-Forum - das Archiv

Grosse Datenmengen in EXCEL imprtieren

Bild

Betrifft: Grosse Datenmengen in EXCEL imprtieren
von: Patric

Geschrieben am: 30.12.2006 09:33:04
Hallo,
ich möchte grösere Datenmengen in EXCEL importieren,
um diese dann mit elementaren Berechnungsmethoden
zu untersuchen.
Die Daten liegen in einer TXT- Datei vor. Das Problem
ist nun, dass die Anz. der erlaubten Zeichen in der ersten
Zeile überschritten wurde.
Es handelt sich um Aktienkurse, die in der 1. zeile die Namen
haben (grösser 1000) und in den Spalten die Kurse je Datum.
hier die Datei: https://www.herber.de/bbs/user/39257.zip
Hat jemand eine Idee?
Gruß,
Patric
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: ingUR

Geschrieben am: 30.12.2006 09:52:54
Hallo, Patric,
die Daten liegen in einem für das einfache Einlesen in EXCEL in ungünstiger Form vor. Zudem ist die sequentielle Bündelung aller 1000 Aktien in einer Datei nicht wirklich geschickt.
Hier wirst Du m.E. nach nur über ein VBA-Programm weiterkommen. Hier könnte man Einzeldateien oder aber eine bessere Datenstruktur erzeugen oder aber gleich die Daten in eine EXCEL-Mappe ablegen, wobei zu berücksichtigen ist, dass je Tabelle maximal 256 Spaten zur Verfügung stehen.
Existiert für jede Aktie das gleiche Datum?
Sofern keine demnächt keine Lösung für Dein Problem vorliegt, werde ich versuchen, mich mit der aufgabe am Nachmittag zu beschäftigen.
nso wie nicht "glücklich"
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: Patric

Geschrieben am: 30.12.2006 09:57:51
Hallo,
vielen Dank für Ihre Mithilfe.
Es ist so, dass diese Datei ein Beispiel war. Und zwar Daten für den November.
Ich habe die gleichen Dateien noch einige male für jeden Monat bis Beginn 2005.
Aber die Aktienkuse liegen für jeden Zeitpunkt/ Datum vor oder sind leer.
Ansonsten kann es nur sein, dass neue Aktien hinzukommen oder rausgenommen
werden.
Vielen Dank.
grüße,
Patric
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: Nepumuk
Geschrieben am: 30.12.2006 10:01:14
Hallo, Patric,
nehmen wir mal diesen Ausschnitt:
1.BK OEST.SPARK. |909943 |AT0000652011;10TACLE STUDIOS AG O.N. |TACL10 |DE000TACL107;123 MULTIMEDIA EO -,10 |A0B56B |
wo fängt da was an und wo hört was auf?
Gruß
Nepumuk
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: Patric

Geschrieben am: 30.12.2006 10:03:56
Hallo,
es geht jeweils bis zum Semikolon:
1.BK OEST.SPARK. |909943 |AT0000652011;10TACLE STUDIOS AG O.N. |TACL10 |DE000TACL107;
123 MULTIMEDIA EO -,10 |A0B56B |
Wirklich brauchen würde ich nur die WKN u./o. den Namen. Dies ist im Beispiel die Nummer nach dem ersten Pipe 909943 und der Text vor dem ersten Pipe BK OEST.SPARK..
Ggf. kann man dies auch wie bei einer DB mit so einer Referenz auflösen. Also nur die WKN in den eigentlichen Spaltenkopf und einen Link auf den Namen.
Gruß,
Patric
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: Nepumuk

Geschrieben am: 30.12.2006 10:36:00
Hallo Patric,
versuch es mal so. In deiner Datei kommen am Ende noch jede Menge Zahlen. Die habe ich jetzt nicht berücksichtigt.
Public Sub prcImport()
    Dim vntFilename As Variant, vntTextArray As Variant, vntCellsArray As Variant
    Dim lngRow As Long
    Dim objFSO As Object
    vntFilename = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If vntFilename = False Then Exit Sub
    Application.ScreenUpdating = False
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    vntTextArray = Split(objFSO.GetFile(vntFilename).OpenAsTextStream.ReadAll, ";")
    For lngRow = 2 To UBound(vntTextArray)
        If Not Cbool(InStr(1, vntTextArray(lngRow - 2), "|")) Then Exit For
        vntCellsArray = Split(vntTextArray(lngRow - 2), "|")
        Range(Cells(lngRow, 1), Cells(lngRow, UBound(vntCellsArray) + 1)).Value = vntCellsArray
    Next
    Columns.AutoFit
    Application.ScreenUpdating = True
End Sub

Gruß
Nepumuk
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: Patric

Geschrieben am: 30.12.2006 14:47:38
Hallo
danke dir.
Die Zahlen sind die eigentlichen Aktienkurse über einen Monat. Du müsstest
da also auch das passende Datum sehen.
Wie führe ich diese Prozedur denn aus?
Einfach dort wo ich auch die makros starte und anlege?
(bei dem VB Editor einfügen und starten?)
Gruß
Patric
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: Nepumuk
Geschrieben am: 30.12.2006 14:54:40
Hallo Patric,
ja, das Datum sehe ich, weiß nur nicht, wie das den anderen Datensätzen zuzuordnen ist.
Die Prozedur muss in ein Standardmodul.
Gruß
Nepumuk
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: ingUR

Geschrieben am: 30.12.2006 16:08:25
Hallo, Patric,
dank Nepumuks Vorarbeit, aus der ich einiges lernen konnte, hier die Umsetzung der Kursdateneinträge als Ergänzung zum Code von Nepumuk (@Nepumuk: spaltenweise einordnen der Datensätze zeilenweise zu den Symbolen gehörig, mit Trennung durch Datum):
Option Explicit
Public Sub prcImport()
Dim vntFilename As Variant, vntTextArray As Variant, vntCellsArray As Variant
Dim lngRow As Long
Dim objFSO As Object
vntFilename = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If vntFilename = False Then Exit Sub
Application.ScreenUpdating = False
Set objFSO = CreateObject("Scripting.FileSystemObject")
vntTextArray = Split(objFSO.GetFile(vntFilename).OpenAsTextStream.ReadAll, ";")
Cells.ClearContents
For lngRow = 2 To UBound(vntTextArray)
If Not CBool(InStr(1, vntTextArray(lngRow - 2), "|")) Then Exit For
vntCellsArray = Split(vntTextArray(lngRow - 2), "|")
Range(Cells(lngRow, 1), Cells(lngRow, UBound(vntCellsArray) + 1)).Value = vntCellsArray
Next
Dim r As Long, offs As Integer, lRow As Long
For lRow = lngRow To UBound(vntTextArray)
If InStr(vntTextArray(lRow - 2), ".") Then
r = 1
offs = offs + 1
Cells(r, 4 + offs) = vntTextArray(lRow - 2)
Else
r = r + 1
If Len(vntTextArray(lRow - 2)) Then
Cells(r, 4 + offs) = CDbl(vntTextArray(lRow - 2))
Else
Cells(r, 4 + offs) = "" ' hier ggf. die Ziffer 0 statt ein Leerstring setzen!
End If
End If
Next
Columns.AutoFit
Application.ScreenUpdating = True
End Sub
Das ganze ist als Standardmodul anzulegen, wobei es wichtig ist, dass der Aufruf bei aktivem Tabellenblatt gestartet wird, in das die Daten eingetragen werden. Ein Löschen der vorherigen Dateneiträge in diesem Arbeitsbaltt erfolgt hier noch ohne ohne Abfrage.
Die Kursdaten werden als Zahlen in die Tabelle eingetragen, wobei jedoch Angaben ohne Inhalt (Leerstring) als Leerzelle gesetzt sind. Hier könnte auch die Null gesetzt werden, wenn Leerzellen bei der numerischen Auswertung zu Problemen kommen sollte.
Gruß,
Uwe
Bild

Betrifft: AW: Korrektur zum Programmcode
von: ingUR

Geschrieben am: 30.12.2006 16:21:04
Hallo, Patric,
beim prüfen der Daten habe ich festgesellt, dass der letzte Wert nicht eingetragen wurde. Daher hier der geänderte Programmcode:
Option Explicit
Public Sub prcImport()
Dim vntFilename As Variant, vntTextArray As Variant, vntCellsArray As Variant
Dim lngRow As Long
Dim objFSO As Object
vntFilename = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If vntFilename = False Then Exit Sub
Application.ScreenUpdating = False
Set objFSO = CreateObject("Scripting.FileSystemObject")
vntTextArray = Split(objFSO.GetFile(vntFilename).OpenAsTextStream.ReadAll, ";")
Cells.ClearContents
For lngRow = 2 To UBound(vntTextArray)
If Not CBool(InStr(1, vntTextArray(lngRow - 2), "|")) Then Exit For
vntCellsArray = Split(vntTextArray(lngRow - 2), "|")
Range(Cells(lngRow, 1), Cells(lngRow, UBound(vntCellsArray) + 1)).Value = vntCellsArray
Next
Dim r As Long, offs As Integer, lRow As Long
For lRow = lngRow - 2 To UBound(vntTextArray)
If InStr(vntTextArray(lRow), ".") Then
r = 1
offs = offs + 1
Cells(r, 3 + offs) = vntTextArray(lRow)
Else
r = r + 1
If IsNumeric(vntTextArray(lRow)) Then
Cells(r, 3 + offs) = CDbl(vntTextArray(lRow))
Else
Cells(r, 3 + offs) = "" ' oder 0
End If
End If
Next
Columns.AutoFit
Application.ScreenUpdating = True
End Sub
Gruß,
Uwe
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: Patric

Geschrieben am: 30.12.2006 16:22:21
Hallo...
danke euch für eure Hilfe...ich werde es alsbald ausprobieren und
euch dann bescheid geben.
Nur eine Frage und Bitte vorweg:
Wie oder Wo lege ich das ganze als Standart-Modul an?? (Sorry aber ich bin was die
Programmierung mit VBA betrifft anfänger!...
danke und Gruß
Patric
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: ingUR

Geschrieben am: 30.12.2006 17:08:21
Hallo, Patric,
in der geöffenten Arbeitsmappe,die die Datentabbele enthalten soll, sind folgende Schritte durchzuführen um den VBA-Code in einem Standardmodul einzufügen:
  1. Öffnen des VBA-Editors mit der Tastenkombination [Alt][F11]
  2. ggf. über Menüpunkt Ansicht das Arbeitsfenster Code öffnen; [F7]
  3. im VBAProjekt-Fenster das aktive Projekt anwählen (ist bei Aufruf vorgewählt; VBAProjekt-Fenster hat den Focus)
  4. Aufruf des Kontexmenüs durch drücken der rechten Maustasten
  5. Befehl Einfügen wählen und Objekt Modul eröffen
  6. auf Arbeitsfläche wechseln und Programmcode über Zwischenspeiche einfügen
    • Code im Beitragsfenster markieren
    • in den Zwischenspeicher kopieren [Strg][C]
    • ins Modulfenster wechseln
    • Programcode aus Zeischenspeicher einfügen [Strg][V]

Viel Erfolg!
Uwe
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: Patric

Geschrieben am: 30.12.2006 18:10:52
Hallo,
vielen Dank.
Das sieht sehr gut aus und genau das, was ich suchte.
ein letzter Punkt ist offen.
Die Kurse der letzten beiden Jahre sind verteilt über jeweils
eine Textdatei, die pro Monat angelegt ist.
Gibt es einen Weg, die Dateien miteinander einfach zu synchronisieren?
D.h. auch zu schauen, ob eine Aktie rausgefallen ist oder dazu kam?
Und ich glaube, dass dann auch die max. Spaltenanzahl überläuft.
Danke nochmal.
Gruß,
Patric
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: Patric

Geschrieben am: 30.12.2006 18:26:50
Hallo,
ich habe noch eine Frage.
Der Import hat wunderbar beim erstenmal funktioniert.
Als ich die Datei ein zweites Mal nun geöffnet hat,
meckert er, dass makros deaktiviert sind.
Woran kann dies liegen?
Und weitere Frage: Sollte ich die Importfkt. für zukünftige
Dateien nehmen können, was müsste da denn modifiziert werden?
ggf. auch in Verbindung mit meiner vorhergehenden Frage zu sehen.
Kann ich die Importfkt. auch über einen Button aus der Tabelle
oder dem Worksheet starten?
Sorry, aber wenn mal was geht, dann folgen die nächsten Fragen und
es schafft Begehrlichkeiten :-)
Danke.
Gruß,
Patric
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: Patric

Geschrieben am: 31.12.2006 09:58:27
Hallo,
ich habe nochmal über die Sache nachgedacht und evt. einen Lösungsansatz:
Man könnte je Monat ja die Daten auf ein eigenes Tabellenblatt importieren.
Ich würde es dann so machen, dass ich ab dem 5. Tabellenblatt die Daten
vorhalte und auf den ersten 3 die Auswertungen mache und das vierte für
Einstellparameter der Statistiken nehme.
Das einzige, wo ich dannHilfe noch brauche ist, wie ich Tabellenblat übergreifend
Abfragen mache. Bspw. Standardabweichungen, Mittelwert und ähnliches.
Und eine weitere Frage für mein Verstädnis: Wenn ich einen Button einbaue,
musste ich gestern Excel erst schliessen und dann wieder öffnen, bevor ich ihn
anwenden konnte. Ich vermute mal, dass dies nicht der eleganteste Weg war.
Danke erstmal
Gruß,
Patric
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: ingUR

Geschrieben am: 01.01.2007 10:42:32
Hallo, Patric,
@Nepumuk hat sein Programm bereits so ausgerichtet, dass Du mit jedem Aufruf die Daten eines Monats, die Du über den "Öffnen"-Dialog aufrufst, in das aktuelle Tabellenblatt schreibst.
Nach dem Einlesen benennst Du das Blatt um und fügst ein neues Blatt ein, wechselst auf dieses und startest den Einlesprozeß erneut.
Bei 24 Monaten ist dieses eine zweiundzwanzigmalige Arbeit um alle Daten in einer EXCEL-Arbeitsmappe zu haben. Die EXCEL-Datei wird sich allerding mit jeder neu eingelesenen TXT-Datei um ca. 1,2 MB im Speicherumfang vergrößern, Du als für 24 Monate eine ca. 28 MB große XLS-Datei erzeugt wirst, die so noch keine weiteren Berechnungsformel und Prozeduren enthält.
Hier nun die etwas abgänderte Prozedur, weiterhin basirend auf dem Grundgerüst von @Nepumuk, dass die ausgewählte einzulesende TXT-Datei in ein neues Arbeitsblatt einliest und das Tabellenblatt nach dem TXT-Dateinamen umbenennt.
Existiert ein Tqbellenblatt mit diesem Namen bereits, wird kein neues Tabellenblatt eingefügt, sondern es wird das bereits existierende Blatt genommen, die bisherigen Daten darauf gelöscht und die neune Daten geschrieben (Ersetzt da bisherige Programm vollstäbndig):
Option Explicit
Public objFSO As New FileSystemObject
Public Sub prcImport()
Dim vntFilename As Variant, vntTextArray As Variant, vntCellsArray As Variant
Dim lngRow As Long
Dim ws As Worksheet, wsName As String
vntFilename = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If vntFilename = False Then Exit Sub
Application.ScreenUpdating = False
Set objFSO = CreateObject("Scripting.FileSystemObject")
wsName = objFSO.GetFileName(vntFilename)
wsName = Left(wsName, InStr(wsName, ".") - 1)
On Error Resume Next
Set ws = Worksheets(wsName)
If ws Is Nothing Then
Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
ws.Name = wsName
End If
With ws
.Cells.ClearContents
vntTextArray = Split(objFSO.getfile(vntFilename).OpenAsTextStream.ReadAll, ";")
For lngRow = 2 To UBound(vntTextArray)
If Not CBool(InStr(1, vntTextArray(lngRow - 2), "|")) Then Exit For
vntCellsArray = Split(vntTextArray(lngRow - 2), "|")
Range(.Cells(lngRow, 1), .Cells(lngRow, UBound(vntCellsArray) + 1)).Value = vntCellsArray
Next
Dim r As Long, offs As Integer, lRow As Long
For lRow = lngRow - 2 To UBound(vntTextArray)
If InStr(vntTextArray(lRow), ".") Then
r = 1
offs = offs + 1
.Cells(r, 3 + offs) = vntTextArray(lRow)
Else
r = r + 1
If IsNumeric(vntTextArray(lRow)) Then
.Cells(r, 3 + offs) = CDbl(vntTextArray(lRow))
Else
.Cells(r, 3 + offs) = "" ' oder 0
End If
End If
Next
.Columns.AutoFit
End With
Set objFSO = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
End Sub
Natürlich kann man den Prozeß zum Einlesen in eine For-Schleife legen, wozu allerdings entweder die Namensliste der TXT-Dateien vorliegen müßte oder aber die TXT-Dateinamen bilden sich nach einer gemeinsamen Regel, z.B. "ak_" & JJJJ & "_" & MM & ".txt". Das die TXT-Dateinen dabei in einemm gemeinsamen Verzeichnis abgelegt sind, wird dabei als gegeben - da sinnvoll - betrachtet.
Auch kann man weitere Operationen VBA- bzw. zellenformelorientiert planen und in die Arbeitsmappe einbauen. Allein die Möglichkeiten und Wege sind so vielfältig und der organsiatorische Aufwand so speziell problembezogen, dass hier m.E. der gegebenen Einstieg durch das Einlesen der Daten zur eigenen Weiterentwicklung mehr als nur einfach ausgearbeitet ausreichend gegeben ist.
So ist das auslesen der OHLC-Kurse eines Monats (um von den Wochen-OHLC-Kursen gar nicht erst zu reden) für die Symbole bereits damit verbunden, dass eine Liste der Symbole verwaltet werden muß, die Zu- und Abgänge von Sympolen erkennt und Umbenenungen ermöglicht - Du hattest bereits auf diese Notwendigkeit hingewiesen.
Ein weiteres Beispiel: Die Relative Stärke (nach Levy) der Papiere zu berechnen, erfordert weiter eine Dynamisierung der Zellenformeln: RS[Symbol] = Close[Symbol(i), t0] / Close[Symbol(i), t0-t], mit veränderlichem Symbol und t.
Auch die Berechnung eines gleitenden Durchschnittswertes muß tabellenblattübergreifend dynamisch in Abhängigkeit vom Symbolparameter und akltuellem t0 bei vorgegebener Überwachungslänge erfolgen.
Bereits diese einfachen Operationen erfordern eine Programmplanung und Umsetzung, die m.E. dem Rahmen des Forums, das wohl vorrangig Hilfe zur weiteren Selbsthilfe geben soll, sprengen würde, da die Lösung nicht von allgemeiner Verwertbarkeit sein werden, denn Bollingerbänder, Portfolio-Auswertungen bis hin zur Risikoanalyse erfordern weitere besondere Schritte, die vermutlich geschickter mit einer geänderten Datensatzverwaltung (externer Datendatei as Datenserver) zu erledigen sein werden, als über die veränderbare Tabellenstruktur der Arbeitsmappe als Datenbank.
Da ich mich allerdings selber mit der Aktienanalyse ein wenig beschäftige (nutzte dazu jedoch die TradeStation und nur für Besondere Auswertungen EXCEL), können wir gerne, wenn Du magst, über dieses Prokjekt in Verbindung bleiben.
Gruß,
Uwe
Bild

Betrifft: AW: Grosse Datenmengen in EXCEL imprtieren
von: Patric

Geschrieben am: 01.01.2007 19:45:04
Hallo Uwe,
danke für die Antwort. Ich werde es morgen im laufe des Tages
ausprobieren und Dir mein Ergebnis mitteilen.
Dann können wir gerne auch über meine Ideen hierzu uns
austauschen.
Danke
Gruß,
Patric
 Bild
Excel-Beispiele zum Thema "Grosse Datenmengen in EXCEL imprtieren"
HTML-Datei im Internet-Explorer mit fester Fenstergrösse