Hoe automatisch het geldende rentepercentage rente tonen in functie van de tijd?

[DirkManet]

Op een speciale bankrekening worden af en toe bedragen geplaatst (er wordt maximaal 1 bedrag per maand gestort). De rente die op dat moment geldt, is vast voor 1 jaar.
Nadat het jaar verstreken is, wordt automatisch de op dat moment geldende rente gekozen, die dan weer een jaar geldig blijft voor die storting.

Ik zou graag een overzicht maken, waarbij in de kolommen de maanden van het jaar staan en in de rijen de rekeningen.
Per 'cel' moet getoond worden:
Het bedrag, de datum van de storting en de geldende rente.


Alhoewel dit op het eerste zicht niet zo moelijk lijkt, is het toch zo complex dat het me niet lukt :-(

Een voorbeeldje:
Rente evolueert als volgt
vanaf 01/01/2012 gelijk aan 1,2%
vanaf 01/02/2014 gelijk aan 1,1 %
vanaf 01/05/2014 gelijk aan 1,0 %
vanaf 01/01/2015 gelijk aan 0,9 %
Stortingen (bv. 100 euro) op de volgende datums: 05-01-2012, 03-02-2014, 04-04-2014, 10-10-2014 en 02-02-2015.

Dan gelden voor de stortingen momenteel de volgende rentes:
05-01-2012 : 0,9 %
03-02-2014 : 0,9 %
04-04-2014 : 1,1 %
10-10-2014 : 1,0 %
02-02-2015 : 0,9 %

 

[OctaFish]

Dat is een vraag die voor jou niet zo moeilijk lijkt, maar dat voor een database wel degelijk is. Wel een vraag die je in Excel overigens heel simpel kunt oplossen, want daar gebruik je de functie Vert.Opzoeken om het juiste percentage uit een tabel op te halen. Die functie pakt namelijk de grootste waarde die kleiner is dan de zoekwaarde, en bij rentepercentages is dat dus zoeken op basis van rentedatum. Je krijgt dan het juiste percentage voor de stortingen.

Nu heet Vert.Opzoeken in de Engelse Excel DLookup, en kent Access toevallig óók een functie DLookup. Het zal toch niet waar zijn? Dit is toch niet dezelfde functionaliteit? Antwoord: twee keer neen, helaas. Het is een compleet andere functie in Access. DLookup gebruik je in Access om specifieke waarden op te zoeken. Je zoekt dus op een datum, en als die datum niet bestaat, dan heb je pech. In het geval van een langdurige renteperiode heb je daar dus niks aan; hooguit vind je het percentage als je op de dag van invoeren van het percentage ook een storting doet.

Kortom: de trucendoos moet open! Daarvoor zijn verschillende opties te verzinnen, en die heb ik gelukkig allemaal al in de cursus Access behandeld . Lees hoofdstuk 17 er maar eens op na, dan vind je geheid een variant die je kunt gebruiken. Vraag is natuurlijk dan: kun je hem ook zelf maken?

 

[patricw]

Ik heb iets soortgelijks met prijzen van een product. Ik heb een tabel gemaakt met de begin- en eind-datum van de prijzen en pak dan op basis van de invoice datum ( between begin en eind) de dan geldende prijs. Zo houd je ook je historie.

 

[OctaFish]

Dat is inderdaad eén van de technieken die ik beschrijf in hoofdstuk 17 .

 

[patricw]

Oh...ik wist niet dat ik al zo ver was in jouw mooie handleiding.

 

[DirkManet]

Allebei hartelijk bedankt. Ik ben de handleiding aan het doorlezen, maar dat is nog (minstens) één stap verwijderd van de oplossing! Gaat wel lukken!

 

[OctaFish]

Ik heb vandaag een uitwerking van hoofdstuk 17 in de vaste cursus topic geplaatst, dus je kunt een werkend voorbeeldje bekijken van de verschillende technieken.

 

[DirkManet]

De query is gelukt, nu de weergave nog!

Beste Octafish, ik heb hst 17 in de praktijk gebracht, de derde oplossing, namelijk historie opbouwen met een einddatum.

In de bijgeleverde Access zul je zien dat ik eerst nog een tussenberekening doe van datums (omdat voor oude stortingen de datum waarop de rente moet gebaseerd worden eerst moet worden herleid naar een datum van minder dan één jaar in het verleden, maar eigenlijk doet dat niet zo ter zake.

Ik heb nu een query 'Geldende rentes' met stortingen en bijbehorende data met vermelding van de maand.
Rekening Datum Bedrag Rentedatum Rente Maand
Rekening A 2011-03-01 € 1.000,00 2015-03-01 0,30% 3
Rekening A 2012-04-03 € 2.000,00 2014-04-03 0,30% 4
Rekening B 2012-02-11 € 8.000,00 2015-02-11 0,70% 2
...

Om dat mooi visueel weer te geven denk je dan aan een kruistabel met in de kolommen de maanden, in de rijen de rekeningen en in de cellen de bedragen, de originele stortingsdatums en het geldende rentepercentage. Dat lukt dus niet, want in een pivot kan je maar één waarde in de cel kwijt.

Ik durf het haast niet te vragen, maar hoe los ik dat nu op?
Alvast weeral bedankt!

 

[OctaFish]

Ik zou in ieder geval je berekening voor de rentedatum

Rentedatum: DateAdd("yyyy";Int(DateDiff("d";[Datum];Date())/365);[Datum])

(hoe ingenieus ook bedacht) vervangen door deze:

Rentedatum: DateSerial(Year(Date())+(Format(Date();"mmdd")<Format([Datum];"mmdd"));Month([Datum]);Day([Datum]))

Dan heb je tenminste een functie die ook werkt in schrikkeljaren .

 

[DirkManet]

Ja, klopt. Is inderdaad beter, maar wel wat moeilijker .

Nog een tip voor mijn weergave/pivot probleem misschien?

 

[OctaFish]

Moeilijker? Hoe kan een standaardfunctie nou moeilijker zijn dan een ingewikkelde berekening? . Je geeft zelf al aan dat jouw vraag onmogelijk te maken is.

Dat lukt dus niet, want in een pivot kan je maar één waarde in de cel kwijt.

De enige manier die ik kan bedenken is met een procedure die een tabel maakt die alle gegevens verzamelt en daar dan een formulier op maken. Maar dat is niet overdreven simpel, al heb ik nog wel een voorbeeldje ooit gemaakt waar je dan wellicht wat aan hebt.

 

[DirkManet]

Ik heb zelf nog even gezocht. Ik heb ergens iets gelezen over het joinen van meerdere pivots, maar heb uiteindelijk gekozen voor een makkelijke oplossing:
Ik combineer de velden in één tekstring met harde returns [(chr(13) & chr(10)] ertussen.
Thanks!

 

[OctaFish]

Dat was ook mijn voorbeeldje geweest .