Excel-Import von CAMT.054.xml Dateien

[hittnau]

Hallo zusammen
Gemäss dem neuen ISO-Standard 20022 werden von den schweizer Banken die ESR-Gutschriftsdateien ab 2018 als XML-Dateien bereitgestellt. Diese Dateien kann man in Excel zwar öffnen, aber es gibt folgendes Problem:
Die Referenznummer des verwendeten Einzahlungsscheins ist 27-stellig. Diese wird im Excel als Exponentialzahl (z.B. 8.11127102E+26) dargestellt. In diversen Foren wird beschrieben, dass man die entsprechende Zelle als "Zahl" oder als "Text" formatieren muss, um die Zahl in der ganzen Länge darstellen zu können. Das ist zwar möglich, wenn man das Feld vorgängig (also bevor man eine lange Zahl eingibt) als "Zahl" oder "Text" formatiert.
Wenn ich aber, wie in meinem Fall, eine XML-Datei öffne, kann ich das ja nicht vorgängig machen. Die ganze Daei wird im XML-Standar geöffnet und das Feld mit der Referenznummer wird automatisch als Exponentialzahl eingelesen. Wenn ich jetzt diese Feld umformatiere, wird die Zahl ab der 15. Stelle nur noch mit Nullen dargestellt (Excel-Standard). Wie kann ich nun diese Zahl in eine 27-stellige Zahl umwandeln?

Im Anhang habe ich einen Printscreen dieser XML-Datei beigefügt.

Für Ihre Hilfe wäre ich sehr dankbar.

Gruss
Urs Angst

 

[Gaby Salvisberg]

Hallo Urs

Hast Du zum Testen eventuell irgendwo eine Beispieldatei mit Phantasiedaten drin, damit man das mal ausprobieren kann?

Herzliche Grüsse
Gaby

 

[hittnau]

Test.xml

Im Anhang habe ich eine Test.xml datei hochgeladen. Musste sie allerding Zippen, da das Format zum Hochladen nicht unterstützt wird.
Danke im voraus

 

[weer]

Grüezi Urs

Frage: Kann man in Zukunft die ESR-Dateien nur noch als XML-Dateien herunterladen? Früher konnte man sie ja auch als txt-Datei (bei der ZKB) oder als V11-Datei (bei Postfinance) herunterladen. - Ich bin nicht mehr in diesem Bereich tätig, deshalb weiss ich es nicht.

Grüsse Niclaus

 

[hittnau]

Hallo Niclaus

Im Moment bieten die meisten Banken den Download beider Varianten (V11- und XML-Datein) an. Aber ab Mitte 2018 werden dann nur noch XML-Dateien angeboten.

Urs

 

[nochEinAndreas]

Etwas holprige Lösung

Hallo Urs,

ich habe dir hier mal eine Lösung hochgeladen. Es ist eine Excel-Datei mit einem Makro. Mit dem Button "Konto-Datei laden" wird er gestartet. Alternativ kannst du auch Strg-k benutzen. Der Makro macht Folgendes:
- Zuerst geht ein Fenster auf, in dem du die Konto-Datei answählst.
- Dann wird automatisch von dieser Datei eine Kopie erzeugt. An den Namen wird _2 angehängt.
- In dieser Kopie wird in die Referenzummer ein nicht anzeigbares Tabulator-Zeichen eingefügt. Damit ist die Nummer für Excel keine Zahl mehr, sondern Text und wird auch als solcher geschrieben.
- Zum Schluss wird diese Datei von Excel geöffnet. Hier hat es allerdings einen kleinen Schönheitsfehler: Da das Format der Datei nicht mehr dem erwarteten entspricht, wird das als Fehlermeldung angezeigt. Die musst du leider von Hand wegklicken. Daraus entstehen aber keine weiteren Probleme.

Probier es mal aus, und eine Rückmeldung wäre schön.

Grüße, Andreas

 

[hittnau]

Hoi Andreas

Das ist absolut genial. Die ganze Weitervararbeitung dieser Datei, d.h. die Verknüpfung mit einer Debitoren-Offene-Posten Datei, habe ich auch in einem VBA-Programm gelöst. Jetzt kann ich Deinen Programmteil einfach am Anfang implementieren und dann müsste es nach meinem Wunsch funktionieren.

Vielen herzlichen Dank.
Urs

 

[weer]

ich habe dir hier mal eine Lösung hochgeladen.

Hallo Andreas

Super Deine Lösung! Vielen Dank!– Ich hoffe, ich durchschaue die Makros und die Function, bevor es einen neuen ISO-Standard geben wird!

Eine Frage habe ich dazu: In der Excel-Datei, die ich schlussendlich erhalte, sind in den Spalten A (ns1:MsgId) und E (ns1:Id) die Werte als Expontial-Zahlen dargestellt: In A2 und in E2: 2.0171E+15 (in der Bearbeitungsleiste: 2017100501699520). Hast Du – oder hat Urs – eine Ahnung, was diese Zahl bedeutet und wie man sie auf einfache Art und Weise formatieren könnte? Die ersten acht Ziffern schauen nach Datum aus. Aber dann?

Eine weitere Frage zu Umlauten, wenn ich aus dem Internet etwas herunterlade, stelle ich in einem eigenen Beitrag. Z. B. bei der Zip-Datei von Andreas.
[Mod-Edit: hier der Link zu Niclaus' Frage betreffs Umlaute: http://www.pctipp.ch/forum/showthread.php?40193-Umlaute-beim-Herunterladen-aus-dem-Internet]

Viele Grüsse Niclaus

 

[hittnau]

Hallo Andreas
Die Spalte A und E kannst Du als "Zahl" formatieren, dann siehst Du den ganzen Inhalt.
z.B: 2017100901701280 Das müsste meiner Meinung nach das Datum und die Zeit sein (Erstellungszeitpunkt).

Da es sich dabei nur um 16 Stellen handelt, funktioniert das mit dem Format "Zahl".

Gruss Urs

 

[weer]

z.B: 2017100901701280 Das müsste meiner Meinung nach das Datum und die Zeit sein (Erstellungszeitpunkt)

Grüezi Urs

Die ersten acht Ziffern als Datum, das leuchtet ein: 20171009 >> 09.10.2017. Aber dann die Zahlenreihe 01701280? ich kann mir das nicht als Uhrzeit erklären.

Grüsse Niclaus

 

[hittnau]

Hallo Niclaus

Habe noch folgende Erklärung dafür gefunden:

"Dieser Tag muss gefüllt werden, um eine effektive Doppeleinreichungskontrolle
über einen Zeitraum von 15 Tagen zu gewährleisten. Deswegen muss die ID
über einen Zeitraum von 15 Tagen eindeutig sein."

Das heisst, das es sich bei der Zahl 01701280 um eine eindeutige Identifikation handeln soll !!??

Ich weiss nicht wofür das gut sein soll

Gruss Urs

 

[weer]

.
Vielen Dank für die Info!

Ich weiss nicht wofür das gut sein soll

Das hängt wohl mit AIA zusammen:

Mit Hilfe des neuen globalen Standards für den automatischen Informationsaustausch über Finanzkonten (AIA) soll die grenzüberschreitende Steuerhinterziehung verhindert werden.

Also, sei vorsichtig! Grüsse Niclaus

 

[nochEinAndreas]

Mal eine Frage an die Schweizer

Noch mal hallo zusammen,

ist das ein rein helvetisches Phänomen, dass es Konto-Auszüge nur noch im XML-Format geben wird?
Ich bekomme meine seit vielen Jahren immer nur als PDF. Und ich habe auch hier in D noch nie etwas davon gehört, dass sich da etwas ändern soll.

Grüße, Andreas

 

[nochEinAndreas]

Noch eine kleine Makro-Erweiterung

Hallo zusammen,

das mit den falsch dargestellten Zahlen in den Spalten A und E könnt ihr automatisch beheben lassen, wenn ihr als letzte Zeile im Sub kontoAuszugLaden() noch einfügt:

Range("A:A,E:E").NumberFormat = "0"

Grüße, Andreas

 

[weer]

ist das ein rein helvetisches Phänomen, dass es Konto-Auszüge nur noch im XML-Format geben wird?

Grüezi Andreas

Zum Glück gilt das Bankgeheimnis nicht mehr so strikt wie auch schon. So darf man Dir also verraten: Es geht hier nicht um Konto-Auszüge - die erhalten auch wir Schweizer als PDF. Es geht um ESR-Gutschriftsdateien. Mit denen haben vor allem Debitoren-Buchhalter zu tun. Es sind die Gutschriftsanzeigen der Finanzinstitute für Einzahlungen, die von den Kunden mit dem sog. orangen Einzahlungsschein (Erlagschein heisst es glaube ich im hohen Norden) getätigt wurden. Neben diesen orangen ESR-Einzahlungsscheinen gibt es noch die "normalen" roten Einzahlungsscheine.

Aber beides nicht mehr lange: "Die heute bekannten Einzahlungsscheine wird es so nicht mehr geben. Einerseits erfolgt eine (europäische?) Vereinheitlichung, anderseits wird ein QR-Code eingesetzt, der die Einzahlungsinformationen maschinell lesbar macht. Diese Neuerungen werden ab Mitte 2018 in Funktion treten. Ab 1. Juli 2020 haben die alten, heute bekannten Einzahlungsscheine ausgedient." - Ich vermute, die Gutschriften, die über diese QR-Einzahlungsscheine getätigt werden, werden dann dem Zahlungsempfänger auch in XML-Listen zur Kenntnis gebracht.

Eigentlich hätten ja wir Schweizer den Nobelpreis für Verhaltensökonomik verdient. Stell Dir vor, wie viele Verhaltensweisen man sich nur schon bei den Einzahlungsscheinen aneignen muss! Es gibt ja dazu auch noch die Begriffe VESR und BESR, die man kennen muss!

Viele Grüsse Niclaus

 

[nochEinAndreas]

Danke für deine ausführlichen Erläuterungen, Niclaus.
Das hat mir die Sache doch sehr erhellt. Ich muss mir also als "Normalbürger" und Nicht-Buchhalter keine Gedanken machen, dass ich mich in naher Zukunft mit so etwas rumschlagen müsste.

Schönen Abend,
Andreas

 

[Gaby Salvisberg]

Ich habe hier der Vollständigkeit halber noch den (angepassten) Code der verwendeten Prozeduren; inkl. Ergänzung aus Post #14.

Option Explicit
Dim verz As String

Sub kontoAuszugLaden()
    Dim pfad As String, datei As String, bName As String, ext As String
    Dim ret As Long
    Dim inhalt As String
    Dim p As Long
    
    ' Es wird erfragt, welche Datei eingelesen werden soll:
    With Application.FileDialog(msoFileDialogOpen)
        If verz <> "" Then .InitialFileName = verz
        .Filters.Add "Konto-Dateien", "*.xlm"
        .InitialView = msoFileDialogViewDetails
        .Title = "Konto-Datei wählen"
         If .Show <> -1 Then Exit Sub
        pfad = .SelectedItems(1)
    End With
    
    ' Aus dem kopletten Pfad werden das Verzeichnis, der Dateiname, und die Eweiterung extrahiert:
    verz = Left(pfad, InStrRev(pfad, "\"))
    datei = Mid(pfad, Len(verz) + 1)
    bName = Left(datei, InStrRev(datei, ".") - 1)
    ext = Mid(datei, InStrRev(datei, ".") + 1)
    
    ' Die komplette Datei wird in die Variable "inhalt" gelesen:
    inhalt = komplettEinlesen(pfad)
    
    ' Wenn der Ausdruck "<Ref>" in der Datei nicht vorkommt, ist es eine falsche Datei:
    p = InStr(inhalt, "<Ref>")
    If p = 0 Then
        MsgBox "Der Datei-Inhalt entspricht nicht dem Konto-Dateiformat", , "Geht nicht!"
        Exit Sub
    End If
    
    ' Zwischen die erste und die zweite Ziffer der Referenznummer wird ein nicht druckbares Zeichen (TAB) eingefügt:
    inhalt = Left(inhalt, p + 5) & Chr(9) & Mid(inhalt, p + 6)
    
    ' Der komplette Datei-Inhalt (Variable "inhalt") wird in eine Datei geschrieben.
    ' Diese Datei steht im selben Verzeichnis wie die Ursprungs-Datei.
    ' Sie bekommt den selben Grundnamen wie die Urpsrungsdatei, mit angehängten "_2".
    komplettSchreiben verz & bName & "_2." & ext, inhalt
    
    ' Die neue Datei wird als XML-Datei geöffnet:
    Workbooks.OpenXML Filename:= _
        verz & bName & "_2." & ext, LoadOption:=xlXmlLoadImportToList
Range("A:A,E:E").NumberFormat = "0"
End Sub

' Einlesen einer kompletten Datei in eine Text-Variable:
Function komplettEinlesen(datei As String) As String
    Dim FF As Long
    
    ' Freie Filenummer erfragen:
    FF = FreeFile
    
    ' Die Textvariable wird vorbereitet: Sie wird in der länge der Datei mit Leerzeichen gefüllt:
    komplettEinlesen = Space(FileLen(datei))
    
    ' Die Datei wird geöffnet, komplett in die Variable eingelesen und wieder geschlossen:
    Open datei For Binary As #FF
    Get #FF, , komplettEinlesen
    Close #FF
End Function

' Schreiben einer Textvariablen als komplette Datei:
Sub komplettSchreiben(datei As String, inhalt As String)
    Dim FF As Long
    
    ' Freie Filenummer erfragen:
    FF = FreeFile
    
    ' Die Datei wird geöffnet, mit der Textvariablen beschrieben und wieder geschlossen:
    Open datei For Binary As #FF
    Put #FF, , inhalt
    Close #FF
End Sub

Liebe Grüsse
Gaby

 

[Hans B]

@nochEinAndreas
Hallo,
da die XML-Dateien von Postfinance immer noch existieren, hatte ich gemäss Artikel von Gaby Salvisberg versucht das makro einzurichten. Aber es fehlt die Basis Datei, die einst hier in diesem Forum existierte.
Kann bitte jemand (oder Andreas) die Datei hier noch einmal ablegen.
Gruss
Hans

 

[nochEinAndreas]

Hallo Hans,

meinst du mit "Basis Datei" meine Makro-Datei? Hier ist sie.

Grüße, Andreas

 

[Marco Zanetti]

Lieber Andreas,
Mit grossem Interesse habe ich deine obige Makro-Datei (Post vom 19.07.2020) heruntergeladen. Ich bin daran ein sehr einfaches Excel zu schreiben (nur sehr wenige Makros), welches erlaubt Bankauszüge "halbautomatisch" für die Buchführung vorzubereiten (Verbuchung aufgrund Standard-Begriffen). Hierzu wäre es super, könnte man die Bankdatei gleich als CAMT-File einlesen.
Leider konnte ich dein Makro nicht ausführen. Es erscheint die Meldung "Laufzeitfehler "91": Objektvariable oder With-Blockvariable ist nicht festgelegt" - Könntest du mir eine Hilfestellung geben, woran das liegt? Hängt die Makro-Ausführung nur bei mir?

Vielen Dank im Voraus für deine Rückmeldung.
Marco