Seite 1 von 2 1 2 LetzteLetzte
Ergebnis 1 bis 10 von 16
  1. #1
    Registriert seit
    26.08.2007
    Beiträge
    137

    ESR-Code generieren

    Hi zusammen

    Wir sind eine Sportschule und haben ca. 250 Mitglieder. Die Buchhaltung erledigen wir seit vielen Jahren mit Banana, was ausreichend ist. Die Verwaltung der Trainierenden und deren Beiträge läuft über eine auf uns zugeschnittene Access-DB (geschrieben für Access 2000), deren Unterstützung nun aber ausläuft und die nun niemand weiterführen wird.

    Deshalb sagten wir uns, dass wir zurück gehen auf etwas ganz Einfaches und die Daten in Excel führen werden. All die Fremdprogramme sind für uns letztlich überdimensioniert und wir brauchen lediglich eine Adressverwaltung mit ein paar frei belegbaren Feldern. Wir schreiben Fakturen über Word, welches uns die Adressen aus Excel holt. Was wir nun aber benötigen, ist ein ESR-Code Generator, damit wir jedem neu Eintretenden einen solchen Code in den Adressstamm eintragen können (er kann fix sein, weil wir den Beitrag, der je nach Anzahl gelöster Quartale variieren kann, beim Fakturieren nicht mitcodieren)

    Frage: Gibt es da Module, die man sich einfach so zulegen kann oder kann man solche Nummern auch online generieren? Wer könnte uns da helfen?

    Danke.

    tron

  2. #2
    Registriert seit
    15.11.2012
    Beiträge
    19
    Hast Du das schon gesehen?
    http://www.gutefrage.net/frage/excel...referenznummer

    Evtl. reicht Dir dieser Rechner?
    http://www.wins.ch/esr01.html

  3. #3
    Registriert seit
    28.02.2010
    Ort
    Grossraum Aarau
    Beiträge
    637

    Esr

    Hallo tron

    Informationen für den Umgang mit ESR (Einzahlungsschein mit Referenznummer) informierst du dich am besten bei der PostFinance oder bei einer Bank.

    So wie ich deiner Frage entnehmen kann, geht es um das Erstellen einer Rechnung aus Adress-Daten und anderen Infos einer Excel Datei, falls noch ein Einzahlungschein erstellt werden muss sind noch weitere Informationen notwendig.

    Für die Rechnung als Information sind zwingend notwendig:
    Die Teilnehmernummer (deines Vereins), die 27-stellige Referenznummer in der Regel in 5-er Zahlengruppen von rechts nach links dargestellt, hier ist als letzte Ziffer (27. Stelle) eine Prüfziffer (PZ) notwendig und der Rechnungsbetrag. Die PZ wird nach Modulo 10 rekursiv berechnet - diese Berechnung wäre in Excel möglich. Mit diesen Informationen ist eine direkte Erfassung im e-banking möglich.

    Falls du noch Einzahlungscheine drucken möchtest informierst du dich bitte bei PostFinance (pdf-File verfügbar) oder bei einer Bank.
    Wichtig ist das die maschinell lesbare Codierzeile im weissen Feld unten rechts auf einem Einzahlungsschein.

    Freundliche Grüsse
    grippen

  4. #4
    Registriert seit
    26.08.2007
    Beiträge
    137
    So, erst mal danke den Hinweisen und Informationen von grippen und Euphrasia. Den einen ESR-Code-Generator hatte ich selber schon gefunden, aber das Problem liegt dort beim Betrag: Auf unseren Rechnungen steht kein Betrag, d.h. die Trainierenden zahlen so viel ein, wie sie trainieren wollen bspw. ein Quartal oder ein Jahr. Somit ist dieses Feld leer, was der Code Generator unter http://www.wins.ch/esr01.html nicht zulässt.

    Aber ich sehe nun langsam, wo die Probleme beim Umstieg von dieser Access-Lösung auf die Excel-Lösung liegen würden: Sie stehen alle im Zusammenhang mit der ganzen ESR-Geschichte. Aber so wie ich Grippen verstehe, müsste es möglich sein, die ESR-Nummer, welche bislang mit der Access-Lösung bei jeder neu eintretenden Person generiert und im Stamm hinterlegt worden ist, auch in Excel generieren zu können. Damit wäre dann auch der Rechnungsdruck (welcher ja bereits besteht und funktioniert) wieder möglich.

    Dann bliebe noch das Problem des Einlesens der Zahlungsdatei, bei der ja via die Referenznummer die Zahlungen erst identifiziert und zugeordnet werden können. Vielleicht noch eine letzte Frage an Grippen: Du hast noch nie eine Lösung gesehen, bei der diese Nummer in Excel generiert worden ist?

    tron

  5. #5
    Registriert seit
    28.02.2010
    Ort
    Grossraum Aarau
    Beiträge
    637

    Esr

    Hallo tron

    Ich denke die Access-Lösung zur Festlegung der Referenznummer müsste in Excel umgesetzt werden können - sofern dir der Algorithmus bekannt ist.

    Eine Lösung in Excel für den Zahlungsverkehr so wie du dir das vorstellst habe ich nicht präsent.

    Ich selber habe Erfahrung in den beiden Codes: 39-er Code und interleaved 2 aus 5 mit der Berechnung der Prüfziffer in Excel für den 39-er Code mit Modulo 43 und für den interleaved 2 aus 5 mit Modulo 10 und einer Gewichtung von 3.

    Freundliche Grüsse
    grippen

  6. #6
    Registriert seit
    13.05.2019
    Beiträge
    6
    Hallo Allerseits

    Da ich genau das gleiche Problem hatte und hier auf die Frage gestossen bin, dachte ich mir, dass ich hier meine Lösung poste.

    Was ich hier gemacht habe ist reines "Reverse Engineering" auf der Basis von Standard-EZS von drei verschiedenen Banken. Also keine Sonderfälle wie Geschäftskonten oder Fremdwährungen. Es kann sein, dass das nicht bei allen Banken gleich funktioniert. Sollte die Kohle die Euch überwiesen werden sollte also zufällig zu mir überwiesen werden, so lehne ich natürlich jede Verantwortung für meinen Code ab.

    Zur Kontrolle habe ich es anschliessend so gemacht, dass ich einfach bei meiner Bank 10 BESR-EZS bestellt und dann verglichen habe, ob mein kleines Excel für diese 10 EZS die korrekte Referenznummer und Codierzeile liefert und das hat geklappt. Evt. müsst ihr also die Formeln etwas anpassen, wenn die Referenzzeile bei Euch etwas anders generiert wird, die Modulo10-Geschichte mit der Checksumme klappt auf jeden Fall.

    Ich habe eine kleine Funktion Modulo10 (s. unten) geschrieben, die aus den Werten…

    ...Laufnummer des EZS in Feld A1 (in der Form "1" oder "123")
    ...Kontonummer (ohne Prüfziffer) in Feld A2 (in der Form "1234560-12", also ohne "-01" am Schluss)
    ...Betrag in Feld A3 (in der Form "12345.67")
    ...PC-Konto der Bank in Feld A4 (in der Form "01-01234-1")

    in Feld A5 eine Referenznummer mit Prüfziffer zusammenstellt
    in Feld A6 eine Referenznummer mit Prüfziffer in 5-er-Block-Guppierung darstellt
    in Feld A7 eine Codierzeile inkl. Betrag mit Prüfziffer und Referenznummer mit Prüfziffer darstellt

    Formel im Feld A5:
    =Modulo10("0110000"&TEXT(A1;"0000000")&"001"&WECHSELN(TEXT(A2;"0");"-";""))

    Anm: Bei 001 habe ich festgestellt, dass zum Teil 001 zum Teil 000 eingefügt werden muss... weiss aber nicht warum.

    Formel im Feld A6*:
    =TEXT(ZAHLENWERT(LINKS(A4;14));"00 00000 00000 00")&TEXT(ZAHLENWERT(RECHTS(A4;13));"000 00000 00000")

    *Links/Rechts ist deshalb notwendig, weil Excel nicht mit so grossen Integern umgehen kann.

    Formel im Feld A7:
    =WENN(A3=0;"042";Modulo10("01"&WECHSELN(TEXT(A3;"0 0000000.00");".";"")))&“>"&A5&"+ "&LINKS(A4;2)&TEXT(ZAHLENWERT(TEIL(A4;SUCHEN("-";A4)+1;SUCHEN("-";A4;4)-4));"000000")&RECHTS(A4;1)&">"

    Nun nur noch die Schriftart des Feldes A7 auf "OCRB" umstellen und die Codierzeile ist fertig und kann z.B. auch mit dem Handy oder Belegleser eingelesen werden.

    Hoffe, dass das hilft. Cheers.

    (C)hristian


    Hier noch die kleine Funktion Modulo10:

    Function Modulo10(ByVal strNummer As String) As String
    ' strNummer darf nur Ziffern zwischen 0 und 9 enthalten!
    Dim intTabelle(0 To 9) As Integer
    Dim intÜbertrag As Integer
    Dim intIndex As Integer

    intTabelle(0) = 0: intTabelle(1) = 9
    intTabelle(2) = 4: intTabelle(3) = 6
    intTabelle(4) = 8: intTabelle(5) = 2
    intTabelle(6) = 7: intTabelle(7) = 1
    intTabelle(8) = 3: intTabelle(9) = 5
    For intIndex = 1 To Len(strNummer)
    intÜbertrag = intTabelle((intÜbertrag + Val(Mid(strNummer, intIndex, 1))) Mod 10)
    Next intIndex
    Modulo10 = strNummer + CStr((10 - intÜbertrag) Mod 10)
    End Function
    Geändert von togsik (13.05.2019 um 16:58 Uhr)

  7. #7
    Registriert seit
    13.06.2015
    Beiträge
    8
    Hallo togsik

    vielen Dank für deinen Input.
    Ich habe folgende Fragen:
    • Mit Kontonummer meinst du die ESR Identifikationsnummer?
    • Mit PC-Konto der Bank meinst du ESR Teilnehmernummer?
    • Mit Laufnumme rmeinst du zBsp die Fakturanummer&Kundennumer?


    Formal A5 ist OK
    Die Formel in A6 liefert mir #name?
    und die Formel in A7 kann ich nicht eingeben/abschliessen, die "00000" wird bemängelt.

    Ich verwende Excel 2010, soweit ich das sehe ist das der Grund für den Fehler in A6. Formel Zahlenwert nicht vorhanden.

  8. #8
    Registriert seit
    13.05.2019
    Beiträge
    6

    Lächeln

    Mit Kontonummer meinst du die ESR Identifikationsnummer?

    Ähm. Vermutlich ist das dasselbe. Bei meinen Bankkonti ist das so. Wissen ist aber was Anderes. Mit der Terminologie habe ich mich nicht beschäftigt, ich wollte nur Quick&Dirty eine eigene Codierzeile machen.

    Mit PC-Konto der Bank meinst du ESR Teilnehmernummer?

    Ja. Ist glaubs dasselbe.

    Mit Laufnummer meinst du zBsp die Fakturanummer&Kundennumer?

    Ja, wie Du mit der Laufnummer umgehst ist aber Deine Sache. Da kannst Du die Kundennummer oder die Fakturanummer reincodieren oder eine Fantasiezahl vergeben, das ist der Bank egal.

    Wenn Du orangene EZS bei der Bank bestellst, dann fragen sie Dich mit welcher Zahl die anfangen sollen und ich habe einfach 10 Stück bestellt mit Startnummer 0 und dann durchprobiert. Deshalb Laufnummer.

    Schlussendlich brauchst Du zur Überprüfung ob es die richtige ESR-Codierzeile ist eh ein paar EZS der Bank. Wenn die Codierzeile dann übereinstimmt, ist gut, wenn nicht ist was falsch. Ich habs einfach anhand von ein paar verschiedenen orangenen EZS ausprobiert und da hat es geklappt. Die "EZS Scannen" Funktion in einem Onlinebanking bringt Dir auch schon einen Hinweis darauf, ob die Codierzeile verstanden/akzeptiert wird oder nicht.

    #name in A6:

    Ja, das heisst, dass die Formel ZAHLENWERT nicht bekannt ist und liegt an Deiner (veralteten) Excel-Version.

    Hast Du es mit der Englischen Version "NUMBERVALUE()" probiert?

    Wenn es nicht funktioniert, musst es über die Funktion TEXT() Funktion machen (Angaben ohne Gewähr):

    Also irgendwie sowas:
    =TEXT(LINKS(A4;14);"00 00000 00000 00")&TEXT(RECHTS(A4;13);"000 00000 00000")

    Hoffe das hilft. Viel Glück beim Ausprobieren.


    PS:
    Habe übrigens festgestellt, dass ich da noch einen Fehler hatte im Feld A5. Irgendeinen Durcheinander mit der Anzahl Nullen.
    =Modulo10("011000"&TEXT(A1;"00000000")&"001"&WECHS ELN(TEXT(A2;"0");"-";""))
    Warum und ob 001 oder 000 in der Mitte, weiss ich wie gesagt nicht.

  9. #9
    Registriert seit
    13.06.2015
    Beiträge
    8

    ESR NUmmer und virtuellen orangen EZ erstellen um der PDF Rech.als PDF mit zu senden

    Vielen Dank für deine umgehende Antwort.

    Ich habe schon mal schnell mit der Funktion Wert (anstelle Zahlenwert) probiert. Das funzt aber nicht einfach so.

    Wir versenden etwa 2500 Rechnungen pro Jahr via Makro automatisch aus Excel Auftragsapplikation 5 Tage nach Lieferung und da unsere Kunden und die Bank es nicht fertigbringen uns bei der Zahlung/Zahlungseingangsmeldung die Rechnungsnummer mitzugeben, müssen wir jeden Zahlungseingang von Hand prüfen und kontieren. Ich möchte der schon vorhandenen Excel Makro Routine noch den "virtuellen" ESR als PDF mitgeben und dann das MT940 File von der Bank einlesen.
    Geändert von fidatex (13.07.2019 um 17:29 Uhr)

  10. #10
    Registriert seit
    13.05.2019
    Beiträge
    6
    Das funzt aber nicht einfach so.

    Etwas genauer müsste ich das schon haben, damit ich Dir helfen kann.

    Ich habe das natürlich so gemacht, dass ich egal welche Konten und Banken verwenden kann. Wenn Du eh nur ein Konto resp. eine Bank brauchst und die Bankkonti und die Kontonummern etc. ohne Bindestriche eingibst, kannst Du es massiv vereinfachen. Das siehst Du in der Antwort auf der nächsten Seite. Da ist die massiv vereinfachte Version mit hardcoded Betrag und Kontonummer.

    1. Referenzzeile in 5-er-Böcken zusammenbasteln

    Funzt es, wenn Du es auseinandernimmst, resp. kommt eine entsprechende Darstellung der Referenznummer in 5-er-Blöcken, wenn Du das hier in irgendeinem Feld machst?

    =TEXT(LINKS(A4;14);"00 00000 00000 00")
    =TEXT(RECHTS(A4;13);"000 00000 00000")

    Ansonsten musst Du die 5-er Blöcke "manuell" erstellen. Braucht halt noch mal ein paar Zellen, ist dann aber auch etwas übersichtlicher:

    1. Du füllst =LINKS(A4;14)&RECHTS(A4;13) in ein Feld ab, sagen wir in B10
    2. In C10 tust Du =LINKS(B10;2)&" "&LINKS(B11;5)&" "&LINKS(B12;5)&" "&LINKS(B13;5)&" "&LINKS(B14;5)&" "&LINKS(B15;5)
    3. In B11 =RECHTS(B10;LÄNGE(B10)-2)
    4. In B12 =RECHTS(B11;LÄNGE(B11)-5)
    5. In B13 =RECHTS(B12;LÄNGE(B12)-5)
    6. In B14 =RECHTS(B13;LÄNGE(B13)-5)
    7. In B15 =RECHTS(B14;LÄNGE(B14)-5)

    Dann hast Du im Feld C10 die Referenzzeile in 2-er/5-er Blöcken.

    2. Codierzeile

    Und in der Formel im Feld A7 musst Du natürlich die Zahlenwert-Formel auch rausschmeissen bei Excel 2010:

    =WENN(A3=0;"042";Modulo10("01"&WECHSELN(TEXT(A3;"0 0000000.00");".";"")))&“>"&A5&"+ "&LINKS(A4;2)&TEXT(ZAHLENWERT(TEIL(A4;SUCHEN("-";A4)+1;SUCHEN("-";A4;4)-4));"000000")&RECHTS(A4;1)&">"

    Wenn das nicht tut und Du eh nur eine Bank brauchst, ersetzt Du einfach den grünen Teil
    =WENN(A3=0;"042";Modulo10("01"&WECHSELN(TEXT(A3;"0 0000000.00");".";"")))&“>"&A5&"+ "&LINKS(A4;2)&TEXT(ZAHLENWERT(TEIL(A4;SUCHEN("-";A4)+1;SUCHEN("-";A4;4)-4));"000000")&RECHTS(A4;1)&">" mit dem hintersten Teil der Codierzeile, die Du auf Deinem EZS drauf hast.

    Also z.B. "010010674" für die Luzerner Kantonalbank oder was auch immer

    Abgesehen davon würd ich mir überlegen ne neuere Excel-Version zuzulegen. Die Masking-Funktionen ("00 000 00" etc.) sind für die Darstellung von Zahlen als strukturierte Zeichenketten echt eine Erleichterung. Sonst musst Du in jedem String zählen, ob die Länge stimmt und wenn nicht mit führenden Nullen oder Leerzeichen auffüllen. Das geht auch, ist aber extrem mühsam. Und überall wo Du anstatt Text wieder eine Zahl hast, musst Du aufpassen, weil es Dir da die führenden Nullen weghaut und die fehlen dann natürlich in der Codierzeile.

    PS: Ich verschicke meine Rechungen per E-Mail und mache in der Mail zusätzlich automatisch eine Codierzeile mit OCRB Schrift rein (wie rechts unten auf dem orangenen EZS), damit die Leute grad direkt mit dem Handy die Codierzeile einlesen und die Rechnung bezahlen können. In der Codierzeile ist meine Rechnungsnummer und der Betrag drin, sodass Nichts schief gehen kann und die Leute brauchen nicht mal die Rechnung ausdrucken, sondern können die Codierzeile grad mit dem Handy auf dem Bildschirm scannen und die Rechnung bezahlen.
    Geändert von togsik (13.07.2019 um 17:56 Uhr)

Seite 1 von 2 1 2 LetzteLetzte

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •