Got a credit card? use our Credit Card & Finance Calculators
Thanks to Anonymous,bruncher,niord,gvonge,Shelford, for Donating to support the site
Which trendline to use?
-
- Lemon Quarter
- Posts: 1138
- Joined: November 23rd, 2019, 4:59 pm
- Has thanked: 17 times
- Been thanked: 356 times
Which trendline to use?
Morning All.
A question for mathematicians and similar out there.
I unitise my portfolios twice monthly (mid month then end month) and am trying to figure out which trendline is the most appropriate to use in Excel to represent them. The choices are here
If your answer is "Polynomial", the Order can be 2-6. If you answer is Moving Average, the Periods can be any number of months.
Any guidance appreciated.
Regards, Newroad
A question for mathematicians and similar out there.
I unitise my portfolios twice monthly (mid month then end month) and am trying to figure out which trendline is the most appropriate to use in Excel to represent them. The choices are here
If your answer is "Polynomial", the Order can be 2-6. If you answer is Moving Average, the Periods can be any number of months.
Any guidance appreciated.
Regards, Newroad
-
- Lemon Half
- Posts: 6140
- Joined: November 21st, 2016, 4:26 pm
- Has thanked: 448 times
- Been thanked: 2369 times
Re: Which trendline to use?
Newroad wrote:A question for mathematicians and similar out there.
It depends what you are measuring by the points. I assume one axis is time but what is the other? Price? Total Return? Portfolio value.
What are you trying to capture with your "trendline"?
It's not a simple answer without knowing what you are wanting. If it were me and I was simply measuring the value of a portfolio over time, with the caveat I wasn't adding to it (new capital), or subtracting from it (drawing an income), then assuming it was linear scaled axis I would want an exponential trendline to reflect the expected compounding.
I would more likely have a non-linear axis and reflect that compounding with a "linear" trendline.
-
- Lemon Slice
- Posts: 671
- Joined: November 4th, 2016, 11:51 am
- Has thanked: 300 times
- Been thanked: 255 times
Re: Which trendline to use?
I'd imagine it's unit value, that's what I use for my unitised portfolio.
I'm interested in the answer because I have a similar graph but no trend lines.
I'm interested in the answer because I have a similar graph but no trend lines.
-
- Lemon Quarter
- Posts: 4519
- Joined: November 8th, 2016, 11:14 pm
- Has thanked: 1642 times
- Been thanked: 1647 times
Re: Which trendline to use?
If the portfolio were a bond with a fixed return then exponential would fit the growth curve exactly. I don't see why exponential should not also be used as an approximation for a more diversified portfolio.
GS
GS
-
- Lemon Quarter
- Posts: 1138
- Joined: November 23rd, 2019, 4:59 pm
- Has thanked: 17 times
- Been thanked: 356 times
Re: Which trendline to use?
Hi Dealtn et al.
It is a set of unitised portfolios, but for purposes of discussion, you may consider it as a single one.
New money does go in, but at the prevailing unit value as per normal accumulation unit process. So, time on the x-axis and unit price/value on the y-axis.
I hope that clarifies adequately?
Regards, Newroad
It is a set of unitised portfolios, but for purposes of discussion, you may consider it as a single one.
New money does go in, but at the prevailing unit value as per normal accumulation unit process. So, time on the x-axis and unit price/value on the y-axis.
I hope that clarifies adequately?
Regards, Newroad
-
- Lemon Half
- Posts: 6140
- Joined: November 21st, 2016, 4:26 pm
- Has thanked: 448 times
- Been thanked: 2369 times
Re: Which trendline to use?
Newroad wrote:Hi Dealtn et al.
It is a set of unitised portfolios, but for purposes of discussion, you may consider it as a single one.
New money does go in, but at the prevailing unit value as per normal accumulation unit process. So, time on the x-axis and unit price/value on the y-axis.
I hope that clarifies adequately?
Regards, Newroad
My answer stands in that case.
-
- Lemon Quarter
- Posts: 1138
- Joined: November 23rd, 2019, 4:59 pm
- Has thanked: 17 times
- Been thanked: 356 times
Re: Which trendline to use?
Thanks Dealtn et al.
That all makes sense - please see below the outcome
It looks a lot like a line of best fit - let's hope it one day again looks more like the exponential curve hoped for That said, the trend from mid 2022 seems OK'ish.
On an unrelated note, it is interesting to see the performance of my experimental pesudo-HYP (P-HYP in the chart) trailing off.
Regards, Newroad
That all makes sense - please see below the outcome
It looks a lot like a line of best fit - let's hope it one day again looks more like the exponential curve hoped for That said, the trend from mid 2022 seems OK'ish.
On an unrelated note, it is interesting to see the performance of my experimental pesudo-HYP (P-HYP in the chart) trailing off.
Regards, Newroad
-
- Lemon Half
- Posts: 8597
- Joined: November 8th, 2016, 5:56 am
- Has thanked: 4559 times
- Been thanked: 3681 times
Re: Which trendline to use?
Newroad wrote:Thanks Dealtn et al.
That all makes sense - please see below the outcome
It looks a lot like a line of best fit - let's hope it one day again looks more like the exponential curve hoped for That said, the trend from mid 2022 seems OK'ish.
On an unrelated note, it is interesting to see the performance of my experimental pesudo-HYP (P-HYP in the chart) trailing off.
Regards, Newroad
One thing I like to do in this kind of thing is look at how the trend changes.
In Excel you can use a "spinner" to increment a field.
Use this field to extract a subset of your data: e.g 12 months
Plot this on top your your data and apply the trendline of your choice to it
Then use the spinner to move that window and watch your trend change and see the parts of the data it is using
It can help highlight and quantify "periods of interest"
This page https://www.pk-anexcelexpert.com/fully-dynamic-chart-in-excel-with-scroll-bar-and-spin-button/ seems to cover the form tools used
-
- Lemon Quarter
- Posts: 1351
- Joined: March 27th, 2017, 11:41 am
- Has thanked: 605 times
- Been thanked: 589 times
Re: Which trendline to use?
A moving average over 3 or 4 points can be useful, but excel will not give a future forecast from a moving average for the good reason that you cannot predict the future by looking out the rear window. Imagine driving your car by looking backwards and holding the steering wheel behind your back, it may seem fine for a little while but eventually you will meet a sharp corner or a T-junction.
None of the others are appropriate for stock prices because even though they may give the appearance of it, they don't follow any strict mathematical pattern.
Any option where Excel offers a future forecast should be ringing a warning bell that this is not a mathematically valid analysis for what is a very unpredictable set of data.
None of the others are appropriate for stock prices because even though they may give the appearance of it, they don't follow any strict mathematical pattern.
Any option where Excel offers a future forecast should be ringing a warning bell that this is not a mathematically valid analysis for what is a very unpredictable set of data.
-
- Lemon Quarter
- Posts: 2373
- Joined: November 4th, 2016, 8:46 pm
- Has thanked: 529 times
- Been thanked: 1014 times
Re: Which trendline to use?
Newroad wrote:
Any guidance appreciated.
Regards, Newroad
You have participated in a similar discussion before, although with a different objective.
Given what you have said, I would select "logarithmic".
See:
https://www.lemonfool.co.uk/viewtopic.php?f=27&t=33722&hilit=llls
Basically, as you know, a logarithmic trendline will tell you how far you are deviating from a consistent percentage y-o-y growth rate.
MDW1954
-
- Lemon Quarter
- Posts: 1138
- Joined: November 23rd, 2019, 4:59 pm
- Has thanked: 17 times
- Been thanked: 356 times
Re: Which trendline to use?
Thanks for reminding me, Malcolm.
I had forgotten. As you say, a slightly different goal, but with some similarities.
I started thinking about this standalone, mainly because the line of best fit didn't seem that useful to me.
Regards, Newroad
I had forgotten. As you say, a slightly different goal, but with some similarities.
I started thinking about this standalone, mainly because the line of best fit didn't seem that useful to me.
Regards, Newroad
-
- Lemon Quarter
- Posts: 1138
- Joined: November 23rd, 2019, 4:59 pm
- Has thanked: 17 times
- Been thanked: 356 times
Re: Which trendline to use?
Hi Servodude.
That spinner/scroll-bar combination is interesting and likely helpful if it would work as hoped - which from your experience suggests so.
I watched the video - I'll need a little time to get my head around it (and it appears that one needs to build it up from scratch - doesn't seem, easily at least, to be able to be retrofitted to an existing chart ).
Regards, Newroad
That spinner/scroll-bar combination is interesting and likely helpful if it would work as hoped - which from your experience suggests so.
I watched the video - I'll need a little time to get my head around it (and it appears that one needs to build it up from scratch - doesn't seem, easily at least, to be able to be retrofitted to an existing chart ).
Regards, Newroad
-
- Lemon Quarter
- Posts: 4683
- Joined: May 31st, 2019, 7:55 pm
- Has thanked: 756 times
- Been thanked: 1516 times
Re: Which trendline to use?
Newroad wrote:Thanks for reminding me, Malcolm.
I had forgotten. As you say, a slightly different goal, but with some similarities.
I started thinking about this standalone, mainly because the line of best fit didn't seem that useful to me.
Regards, Newroad
Roughly approximating your data series and log linear regression (LLR) =linest(ln(a1:a46)) suggests a -0.135% slope whilst CAGR suggests -0.038%. The y=mx+c slope closer fits with that LLR value
Code: Select all
100
100.7
100.7
102.31
103.78
102.58
101.61
102.3
103.1
105.64
103.91
104.22
105.91
101.66
100.26
99.48
97.75
97.45
100.88
99.36
98.47
95.32
96.85
93.49
92.24
95.89
97.75
100.73
97.45
96.43
93.45
92.91
94.65
97.31
97.77
96.12
96.12
97.56
99.53
101.46
98.3
97.23
98.53
99.35
98.8
98.29
-0.135% =LINEST(LN(B1:B46))
-0.038% CAGR
-
- Lemon Half
- Posts: 8597
- Joined: November 8th, 2016, 5:56 am
- Has thanked: 4559 times
- Been thanked: 3681 times
Re: Which trendline to use?
Newroad wrote:Hi Servodude.
That spinner/scroll-bar combination is interesting and likely helpful if it would work as hoped - which from your experience suggests so.
I watched the video - I'll need a little time to get my head around it (and it appears that one needs to build it up from scratch - doesn't seem, easily at least, to be able to be retrofitted to an existing chart ).
Regards, Newroad
OK as I find myself with nothing to do at the moment lets try an example:
I've mocked up some simple data and plotted it in the image below
I have also made an extra short series using indirect to extract a window of that data
Cell G3 contains
Code: Select all
=INDIRECT("a" & ($F3 + $H$1))
and H3 contains
Code: Select all
=INDIRECT("b" & ($F3 + $H$1))
these are then "filled down" beside the 1..12 numbers beside them in column F
(Those numbers are really just there to make the building of the indirect string as obviously done as possible)
it is just concatenating the letter with the result of that addition (incase anyone is not aware $ is used to keep that part of the cell reference constant)
Cell H1 is where I have told the scroll bar (via its "properties") to put its value (for the purposes of the calculations though this could just be a cell you type in)
I then select that window worth of data (both columns) and "paste special"->"new series" over the existing graph
Then add a trendline - extending by X days in to the future
And I can now drag that bar to see where the trend was for any 12 consecutive samples
I hope that makes sense
-sd
-
- Lemon Quarter
- Posts: 1138
- Joined: November 23rd, 2019, 4:59 pm
- Has thanked: 17 times
- Been thanked: 356 times
Re: Which trendline to use?
Thanks, ServoDude.
I won't be able to try it in anger for about two weeks due to travels, but will report back when I do.
In the meantime, any chance you could add a second column of example data, if not too onerous? That closer mirrors what I need, i.e. multiple columns of data represented on a single graph, but with the trend line only on one.
Regards, Newroad
I won't be able to try it in anger for about two weeks due to travels, but will report back when I do.
In the meantime, any chance you could add a second column of example data, if not too onerous? That closer mirrors what I need, i.e. multiple columns of data represented on a single graph, but with the trend line only on one.
Regards, Newroad
Return to “Portfolio Management & Review”
Who is online
Users browsing this forum: No registered users and 5 guests