Volatiliteit berekening met correctie.

[Heesen]

He hallo iedereen,

Ik ben nu al een poosje aan het 'strugelen' dus ik hoop dat ik er hier met jullie hulp uit kom.

Ik moet voor een onderzoeksproject waar ik aan werk, een volatiliteits- berekening uitvoeren. Dus in essentie is dat in Xtijd stijgt of daalt een prijs Xpercentage gemiddeld. Echter dat lukt wel, maar ik heb hulp nodig bij een iets complexere variant:

Ik moet weten hoeveel een prijs opéén volgend stijgt of daalt, tot dat een correctie (prijs kentering) van Xpercentage zich voordoet. Oftwel de prijs stijgt bijvoorbeeld 4% en daarna corrigeert (daalt) die 1%. De correctie moet ook opéénvolgend plaatsvinden. Hoeveel die mag corrigeren moet ik in kunnen stellen, oftewel Xpercentage. Indien die dat percentage niet aantikt moet die door blijven tellen en indien die dat percentage wel bereikt moet die overnieuw beginnen met tellen.

En dan 'last but not least' moet ik tellen hoe vaak dat voorkomt in een kolom met iets van 20.000 getallen, dus zo veel mogelijk automatiseren middels een formule zou mij hierbij helpen.

Dus als Xpercentage correctie is ingesteld op 1% en de volatiliteitscijfers per uur zijn bijvoorbeeld:

0,5%
0,8%
-0,3%
1,6%
2,7%
-0,4%
-0,4%
-0,3%................. Tot hier is de prijs 5,3% gestegen en daarna daalde die 1,1% (-0,4%,-0,4%,-0,3%) opeenvolgend, dus een correctie van >1% na een stijging van 5,3%, deze twee getallen moet ik weten. Vanaf hier moet Excell opnieuw beginnen met tellen met de rest van de reeks in de kolom.

Ik heb een voorbeeld werkblad bijgevoegd als bijlage.

Ik hoop dat jullie mij kunnen helpen, alvast heel erg bedankt!

 

[JEC.]

Bedoel je het zo? Je kunt even checken of eea klopt want ik weet niet of ik het helemaal juist begrijp.
Bij het laatste deel (in het rood) snap ik niet wat er moet gebeuren.

Als je je percentage verandert in cel O4 gaat de macro lopen.

 

[cow18]

@JV, ongeldige bijlage

 

[JEC.]

Apart.. ik kan hem openen. Probeer het nu nog eens

 

[cow18]

nu ook, 20:55 had je hem gepost en 20:59 heb je hem veranderd, ik denk dat ik net rond die 20:59 op die bijlage geklikt heb.

Ik begrijp ook eea niet.
een opeenvolgende daling van bv. 1.5% moet je dan daar 1.5 of 1.5% = 0.015 zien staan ?

 

[Heesen]

Oh wouw, dat is snel!

Dit ziet er inderdaad al veelbelovend uit, echter ik zie nu niet hoe de getallen in de kolom "Cummulatief stijgende prijs tot correctie Xpercentage"zijn berekend. Maar het lijkt wel redelijk aannemelijk.

Excuses voor de onduidelijkheid, alle eenheden zijn al omgerekend naar percentages, dus Xperncentage 1,5 is in dit geval ook 1,5 als eenheid.

Bij de kolom in het rood, daar wordt bedoeld, hoe vaak komt het voor, dat de prijs 0-1% (of 1-2%,2-3%,3-4%...........>15%) stijgt tot dat de correctie van Xpercentage (1,5 in dit geval) zich voordoet.
Oftwel bij rij 3-4%, is de frequentie bijvoorbeeld 5, dan betekent dat: de prijs loopt 5 keer op tot 3-4%, tot dat de prijs 1,5% corrigeert (daalt).

Echt al super bedankt in elk geval. Reuze blij met jullie hulp!

 

[JEC.]

Gaat het alleen om dalen? Ik heb stijgen ook meegenomen.
Met dat de waarde hoger dan 1,5 of lager dan -1,5 komt, zal de telling op opnieuw beginnen in het voorbeeld

 

[Heesen]

Oh okiedo. Nee het gaat alleen om dalen eigenlijk.

 

[JEC.]

Gebruik je office 365?

 

[JEC.]

Nieuwe poging voor het eerste deel.
Frequentie per cumulatieve stijging staat er nog niet.

 

[cow18]

ik ben niet mee aan het rekenen, maar zit er geen wiskundige denkfout in bovenstaande.
Veronderstel dat je x periodes van stijging en/of daling hebt en je sommeert die percentages, dan kom je op een bepaald getal uit, bv. je wilt op 1.5% uitkomen in een aaneensluitende periode van dalingen.
Maar eigenlijk moet je toch een product doen over die periodes van (1-daling in die periode) en dat over die x periodes. Het is dan bijlange niet zeker of je op 98.5% zal uitkomen, wel ergens in de buurt.

Tenzij je nu plots afkomt en zegt dat die percentages afgetoetst worden tov een vast getal en niet tov de waarde van de voorgaande periode.

 

[JEC.]

daarna daalde die 1,1% (-0,4%,-0,4%,-0,3%)

Ik ben uitgegaan van TS:
- Als de aaneengesloten periode van dalingen wordt beëindigd door een stijging, begint de sommering van dalingen weer opnieuw.
- Als het aangegeven percentage (bvb 1,5%) van achtereenvolgende dalingen wordt bereikt, begint alles opnieuw.

Of dit uiteindelijk juiste antwoorden oplevert vraag ik me ook af. Wordt er uitgegaan van percentagepunten of moet er, zoals je al zegt, vermenigvuldigd worden...

 

[cow18]

zie bijlage
in de E-kolom probeer ik bovenstaande probleemstelling te duiden.
Volgens die berekening zou je aan het einde van de rit op 100,47% van het begin zitten via product
Volgens het gewoon optellen zit je op +0.78%.
Kijk je naar de grafiek, dan loopt de bruine lijn soms boven, soms onder, dus hier valt het nog mee.

Kolom B en C, wanneer oneven dan ben je gewoon in een lopende periode, is die even, dan ben je aan het corrigeren.

1e draaitabel heeft per fase de som en het aantal periodes (uren)
2e draaitabel heeft de frekwentie

 

[Heesen]

Allereerst hartelijk dank voor alle reacties, ik waardeer jullie input.

Ik begrijp jullie verwarring en eigenlijk hebben jullie gelijk, maar in dit geval kloppen de variabelen wel denk ik. Aanvankelijk dacht ik 'less is more' wanneer je met dit soort materie uit wil leggen, wat je precies wilt meten. Maar wellicht helpt wat extra context. Het onderzoeksproject waar ik aan werk heeft te maken met een tradingsysteem die orders geautomatiseerd inschiet, wanneer er een kentering van de prijs plaats vindt. Dat maakt het direct iets lastiger, want je hebt geen absolute waardes ten opzichte van waar je het tegen afzet.

Je hebt per uur een prijs in dit geval, en de volatiliteit afgezet tegen de waarde in het vorige uur wordt berekend in %, bv.:

uur 1: 20.000
uur 2: 20.200 1.00%
uur 3: 20.150 -0.25%
uur 4: 20.125 -0.12%
uur 5: 19.800 -1.61%
uur 6: 20.100 1.52%
uur 7: 20.800 3.48%
uur 8: 20.725 -0.36%
uur 9: 22.000 6.15%
uur 10:21.200 -3.64%
uur 11:21.800 2.83%

Nu is het de bedoeling dat gemeten wordt tot dat Xpercentage 1,5 (oftwel een opéénvolgende daling van de prijs van 1,5 in dit geval) of meer bereikt wordt, en daarna begint die opnieuw met tellen.

uur 1: 20.000
uur 2: 20.200 1.00% 1.00%
uur 3: 20.150 -0.25% 0.75%
uur 4: 20.125 -0.12% 0.63%
uur 5: 19.800 -1.61% .........(indien 1,5 niet bereikt was of Xpercetage is een hogere waarde, dan zou die doortellen met dit getal:-0.98%)......=>Xpercentage 1,5%, telling begint opnieuw
uur 6: 20.100 1.52% 1.52%
uur 7: 20.800 3.48% 5.00%
uur 8: 20.725 -0.36% 4.64%
uur 9: 22.000 6.15% 10.79%
uur 10:21.200 -3.64% ........ (7.15%).........................=>Xpercentage 1,5%, telling begint opnieuw
uur 11:21.800 2.83% 2.83%

Dus de frequentie dat dit voorkomt in dit geval is 2, in deze reeks.

Omtrent de tabel, die ziet er dan als volgt uit:

Cummulatieve stijging tot correctie Xpercentage:

0-1% 1
1-2% 0
2-3% 0
3-4% 0
4-5% 0
5-6% 0
6-7% 0
7-8% 0
8-9% 0
9-10% 0
10-11% 1
11-12% 0
12-13% 0
13-14% 0
14-15% 0
15>% 0

1 keer liep het cumulatieve percentage op tot 0.63%, voordat die Xpercentage bereikte, dus dat is 1 keer, 0-1%
1 keer liep het cumulatieve percentage op tot 10.79%, voordat die Xpercentage bereikte, dus dat is 1 keer, 10-11%

Ik hoop dat dit het één en ander verduidelijkt. Misschien helpt het als ik nog een kolom toevoeg met prijzen.

 

[cow18]

in het laatst meegestuurde bestand was de kentering eventjes op 0,9% gezet waardoor er 9 kenteringen waren, af te leiden uit de rode strepen op de 2e grafiek.
Als je dan kijkt naar de frekwenties dan zijn er maar 7 geteld, want er zijn er eigenlijk ook 2 negatieve, dus die had ik eigenlijk ook moeten aangeven.
Ga je akkoord met deze manier van tellen ? (bekijk ter verduidelijking de 2e grafiek)

Verder zou ik anders zeggen, als je toch de absolute getallen hebt (dus die 20.000 etc) dan is het gemakkelijker/minder foutgevoelig met die te tellen dan met de door jouw aangeleverde percentages.

Ik denk dat ik je vraag juist geïnterpreteerd heb, even op een zondagmiddag snel je vorige reactie lezend, zonder het nog eens te checken.

 

[Heesen]

Nou het ziet er echt heel goed uit cow 18! Ik moet heel eerlijk bekennen dat mijn kennis en kunde op dit moment even tekort schiet om snel te kunnen verifiëren of het ook echt klopt zo. Maar het ziet er echt heel goed uit en lijkt aannemelijk. Ik moet mij nog even verdiepen in de draaitabel functie e.d. daar ben ik niet bekend mee.

Ik heb in ieder geval even de prijzen toegevoegd dan weten jullie ook wat je meet.

 

[cow18]

ik heb je absolute waarden meegenomen, maar verder nog niets aan veranderd.
Kolom C en D zijn de B-waarde gedeeld door respektievelijk A2 of de A-waarde van de rij er voor.
Jouw E-kolom verschilt van beiden, dus is het even de vraag hoe jij indertijd die kolom berekend had.
Dan kan ik verder werken tenzij je nu zegt, dat C of D de betere optie is.

 

[Heesen]

Dat heb je scherp gezien. Er is denk ik een afrondingsverschil ontstaan in een tussenkollom destijds en daar is mee doorgerekend. Kolom D is juist.

 

[cow18]

aangepaste versie met nu enkel die vroegere kolom D.
Grafiek toont nu ook de echte beurswaarden.
Bij nieuwe gegevens plak je die in tabblad "Nieuwe Data" en laat je de macro "Nieuwe_Opgave" lopen.

 

[Heesen]

Dit is echt fantastisch! Echt heel blij mee en diep respect voor de kennis en kunde.

Nu heb ik wellicht nog één vraagje, ik hoop niet dat ik te veel het hemd van het lijf vraag. Deze resultaten zijn al heel waardevol, nu zie ik alleen nog in de grafiek drie keer een momentum die ik ook graag mee zou willen tellen als een correctie in het geval Xpercentage ingesteld staat op 1% (in dit geval).

Nu worden ze alleen niet meegeteld als correctie omdat het geen opéénvolgende dalingen zijn, omdat er micro stijgingen tussendoor zitten. Is er nog een mogelijkheid, waarbij die rekening houdt met een bepaalde afwijking/marge en indien kleiner dan die afwijking/marge dan telt die ook als correctie mee.

Ik voeg nog een voorbeeld grafiek toe in de bijlage waarin die drie momentums zijn omcirkeld.