[EXCEL 2003] Schuivend gemiddelde over 3 maanden*

[Rogier V]

Edit: Ik ben totaal vergeten een goede titel te plaatsen

Voor mijn scriptie moet in Excel een variabele berekend worden. Deze variabele is gedefinieerd als het ‘moving average’ van het uitgegeven vermogen in dollars gedurende de 3 maanden voor de uitgiftedatum. De lijst beslaat ongeveer 7500 issue datums, wat wil zeggen dat het enorm lang duurt om de 3 maanden-moving average handmatig in te voeren omdat er in elke 3-maandstermijn steeds een ander aantal uitgiftes heeft plaatsgevonden. Het mooiste zou zijn als je een formule in de moving average cell zet, die dus steeds het gemiddelde berekent van een telkens ander aantal uitgegeven volumes in dollars, en die dan vervolgens doortrekt naar beneden. Echter wil het mijn niet lukken om precies het uitgegeven volume in dollars van alle uitgiftes, die binnen 3 maanden voor de betreffende uitgiftedatum hebben plaatsgevonden, te pakken.

Het probleem beschrijf ik hieronder door middel van een datavoorbeeld:

Voorbeeld:

Issue Date	Company	Uitgegeven Volume in Dollars	Moving Average
16-10-79	Bedrijf A	7,650
29-11-79	Bedrijf B	2,800
20-12-79	Bedrijf C	7,375
15-01-80	Bedrijf D	14,988	5,941
05-03-80	Bedrijf E	25,500	11,1812
01-04-80	Bedrijf F	9,188	20,2443
09-04-80	Bedrijf G	11,000	16,5584
17-04-80	Bedrijf H	5,700	15,2295
22-04-80	Bedrijf I	5,625	12,8476
23-04-80	Bedrijf J	9,250	11,4037
29-04-80	Bedrijf K	44,550	11,0448
06-05-80	Bedrijf L	8,438	15,2259
21-05-80	Bedrijf M	9,875	16,96610

Waarbij de moving averages alsvolgt berekend moeten worden:
1: Gemiddelde uitgegeven volume van alle offerings die tussen 15 oktober 1979 en 15 januari 1980 hebben plaatsgevonden (Bedrijf A, B en C).
2: Gemiddelde uitgegeven volume van alle offerings die tussen 5 december 1979 en 5 maart 1980 hebben plaatsgevonden (Bedrijf C en D).
3: Gemiddelde uitgegeven volume van alle offerings die tussen 1 januari 1980 en 1 april 1980 hebben plaatsgevonden (Bedrijf D, en E).
4: Gemiddelde uitgegeven volume van van alle offerings die tussen 9 januari 1980 en 9 april 1980 hebben plaatsgevonden (Bedrijf D, E en F).
5: Gemiddelde uitgegeven volume van alle offerings die tussen 17 januari 1980 en 17 april 1980 hebben plaatsgevonden (Bedrijf E, F en G).
6: Gemiddelde uitgegeven volume van alle offerings die tussen 22 januari 1980 en 22 april 1980 hebben plaatsgevonden (Bedrijf E, F, G en H).
7: Gemiddelde uitgegeven volume alle offerings die tussen 23 januari 1980 en 23 april 1980 hebben plaatsgevonden (Bedrijf E, F, G, H en I).
8: Gemiddelde uitgegeven volume van alle offerings die tussen 29 januari 1980 en 29 april 1980 hebben plaatsgevonden (Bedrijf E, F, G. H, I en J).
9: Gemiddelde uitgegeven volume van alle offerings die tussen 6 februari 1980 en 6 mei 1980 hebben plaatsgevonden (Bedrijf E, F, G. H, I, J en K).
10: Gemiddelde uitgegeven volume van alle offerings die tussen 21 februari 1980 en 21 mei 1980 hebben plaatsgevonden (Bedrijf E, F, G. H, I en L).

Overige opmerkingen:
Het komt voor dat er op 1 datum meerdere uitgiftes zijn gedaan. Voor deze specifieke uitgiftedatum moet de moving average dus hetzelfde zijn. Dus zijn er 3 uitgiftes geweest op 3 maart 1980, dan moeten deze 3 uitgiftes ook alledrie hetzelfde moving average hebben, namelijk het gemiddelde uitgegeven vermogen vanaf 4 december 1979 tot 3 maart 1980. Er worden niet dagelijks aandelen uitgegeven dus het komt voor dat er op sommige data geen uitgiftes zijn gedaan, deze data ontbreekt dus ook in de dataset.

[Tukk]

Euh, ik hoop dat ik het helemaal snap.

Volgens mij wil je te moeilijk doen, Excel is geen heilige graal en wordt met regelmaat misbruikt voor zaken waar andere pakketten beter voor te gebruiken zijn.

Wat jij wil is IMHO het best op te lossen met een database (bv Sql server, Oralce, MySQL) waar je de data inzet die je wil en de berekeningen die je wil kan je oplossen door een VIEW te definieren die per datum bepaald wat nu de moving avarages zijn. De taal SQL is IMHO bijna uitgevonden voor dit probleem en is vele malen flexibeler dan elke exel oplossing.

[ Voor 7% gewijzigd door Tukk op 04-07-2007 23:57 ]

[Verwijderd]

Het gaat hier om mijn afstudeerscriptie (Rogier V heeft als stand-in mijn probleem geplaatst). Helaas heb ik onvoldoende tijd om nu SQL te leren en ik zou het probleem graag oplossen met een macro of iets dergelijks.

[Lustucru]

Tukk schreef op woensdag 04 juli 2007 @ 23:56:
[...] Excel is geen heilige graal en wordt met regelmaat misbruikt voor zaken waar andere pakketten beter voor te gebruiken zijn.
[knip]

Dat klopt maar voor dit soort (financiele) berekeningen zou ik nu juist wél een spreadsheet nemen. Daarzijn ze ongeveer voor gemaakt .Met SQL wordt het een tamelijk omslachtige onderneming.

on topic:
stapsgewijs is het niet zo moeilijk. Met zelfde.dag() bepaal je de datum van drie maanden geleden, met vergelijken en index haal je het bereik op en dan gemiddelde eroverheen. Het enige waar je even mee uit moet kijken is dat vergelijken de grootste waarde neemt die kleiner of gelijk is aan je zoekwaarde. Afhankelijk of de begindatum er wel of niet bij hoort (18-03-2007 t/m 18-06-2007 of 19-03-2007 t/m 18-06-2007) moet je wat goochelen met +1 en -1.

Dus ongeveer zo:

code:

=GEMIDDELDE(INDEX(optelbereik;VERGELIJKEN(ZELFDE.DAG(datum;-3);datumbereik;1)+1):laatste waarde) )

[ Voor 3% gewijzigd door Lustucru op 05-07-2007 01:09 ]

[Verwijderd]

Ik ben nog even aan de slag gegaan met de formule. Het probleem dat ik nu nog heb is het feit dat Excel met de VERGELIJKEN formule nu exact de datum pakt van 3 maanden voor de uitgiftedatum (het corrigeren voor 18-03-2007 of 19-03-2007 is allemaal wel gelukt) en dus niet de waarnemingen van de tussenliggende data (ik heb de datum van 3 maanden voor de uitgifte (+1 dag) bepaald door middel van de DATE formule, misschien is dit waar het fout gaat...). Excel pakt nu helaas dus geen "datarange" maar gewoon die ene expliciete datum + waarneming. Bijkomend probleem is hierbij dat er niet op elke dag van het jaar een uitgifte geweest is en excel in het geval dat er exact 3 maanden geen uitgifte is geweest dan dus "N/A" vermeldt in plaats van naar de tussenliggende data te kijken.

Het probleem zit hem dus voornamelijk in het "van datum tot datum" onderdeel en het feit dat er niet op elke datum een waarneming aanwezig is en op sommige data meerdere waarnemingen zijn (meerdere uitgiftes op 1 dag).

[Lustucru]

Post even de formule zoals je hem nu hebt

het bereik moet natuurlijk iets zijn als (bv op regel 20:)

code:

=gemiddelde({indexopzoekformules}:C20)

En als hij n/b geeft dan check even de derde parameter van vergelijken(): die moet '1' zijn, dwz benaderen = waar.

Eventueel kun je ook experimenteren met som.als()/aantal.als()

[ Voor 83% gewijzigd door Lustucru op 05-07-2007 13:29 ]

[Verwijderd]

Schrik niet, ik heb nu de volgende formule:

=AVERAGE(INDEX($F$2:G24;(MATCH(DATE(YEAR(F25);MONTH(F25)-3;DAY(F25)+1);$F$2:F24;1));2):G24)

Mijn data staan in kolom F en de waarnemingen in G. Dit is dus de formule die op regel 25 komt te staan. Ik heb je laatste opmerking al verwerkt, hij geeft nu inderdaad geen N/A's meer. Voor de eerste waarneming werkt hij gewoon goed . Als ik hem doortrek dan werkt hij nog niet optimaal, weet nog niet wat hij fout doet. En nog 1 dingetje , hij neemt nu de average tot de laatste cel (G24) voor de offering, in verband met dubbele offerings op 1 datum zou ik graag willen dat hij het gemiddelde neemt tot de offerdate.

Dus bijvoorbeeld:

08-11-1979 10.31
14-11-1979 7.25
14-11-1979 8.63 => dat is dus voor deze datum niet 7.25 meeneemt maar pakt tot en met 10.31.

[Verwijderd]

Meeste is gelukt, ook het laatste 'probleem' van de dubbele uitgiftedata, heb die hele index rits er nogmaals achtergeplakt ipv cel G24, lijkt te werken, elke datum krijgt nu hetzelfde gemiddelde ongeacht hoeveel uitgiftes er op die ene dag hebben plaatsgevonden

Er is nog 1 dingetje, wat je al zei:

Het enige waar je even mee uit moet kijken is dat vergelijken de grootste waarde neemt die kleiner of gelijk is aan je zoekwaarde.

Op het moment dat er dus op de datum van 3 maanden ervoor (+1 dag) geen waarneming is, dan neemt ie de voorlaatste ipv de eerstvolgende... Dat is het enige wat nog niet goed werkt nu...en wat volgens mij met +1 of -1 niet kan worden verholpen, er is niet op elke datum een waarneming namelijk.

[Lustucru]

Die date() constructie is onnodig moeilijk en bovendien fout als de driemaandsperiode over een jaargrens heen gaat. Qua index() kun je beter de andere variant nemen, die is simpeler in dit geval.

Als je een datum wilt zoeken vóór de huidge datum dan moet je die index(vergelijken()) opdracht herhalen.

code:

=GEMIDDELDE(INDEX($G$2:G24;VERGELIJKEN(ZELFDE.DAG(F25;-3);F$2:F25;1)+1) :
                         INDEX($G$2:G24;VERGELIJKEN(F25-1;F$2:F25;1))

In dit geval neemt hij voor 19-07-2007 alle datums van 20-07-2007 tot en met 18-07-2007

[Verwijderd]

Geweldig, m'n variabelen zijn gemaakt!! Hartelijk bedankt!

En dat van die datum is inderdaad wat omslachtig, maar werkt wel hoor als een driemaandsperiode over een jaargrens heengaat, dat heb ik van tevoren even gecheckt.

Nogmaals bedankt!