Google Answers Logo
View Question
 
Q: Using Excel to predict future results using past data ( No Answer,   9 Comments )
Question  
Subject: Using Excel to predict future results using past data
Category: Business and Money > Accounting
Asked by: daveusa-ga
List Price: $3.00
Posted: 06 Apr 2006 05:27 PDT
Expires: 15 Apr 2006 01:23 PDT
Question ID: 716090
Hi, I'm a first time user, so apologies if i miss something.

I need to calculate (in excel), predicted values for the next 90 days
daily returns using data from the previous 90 days returns (given
below). I am unable to find out how to do this.  I am looking for
someone who can calculate the next 90 days returns and tell me how
they did it on excel!!

DAY	RETURN
1	-0.2691
2	-0.0899
3	-0.9901
4	0.3636
5	0.1812
6	0.5425
7	0.2698
8	0.6278
9	1.4260
10	0.4394
11	-0.0875
12	0.1751
13	-0.1748
14	0.1751
15	-0.6993
16	-0.5282
17	-0.3540
18	0.8881
19	0.7923
20	-1.4847
21	-0.3546
22	1.0676
23	0.7042
24	-0.3497
25	1.0526
26	-0.2604
27	-0.2611
28	2.3560
29	-0.0853
30	-1.8771
31	-1.1305
32	-1.0554
33	0.8000
34	0.0882
35	-1.8502
36	-1.6158
37	0.7299
38	-1.1775
39	-1.1916
40	-1.8553
41	1.8904
42	0.1855
43	1.3889
44	0.6393
45	0.9982
46	-1.8868
47	-0.1832
48	2.7523
49	0.5357
50	1.5098
51	2.1872
52	-0.5993
53	1.4643
54	-0.9338
55	-0.5998
56	1.0345
57	1.6212
58	0.5038
59	0.7519
60	-1.1609
61	1.0906
62	1.5768
63	-0.7353
64	-0.0823
65	1.2356
66	-1.1391
67	-0.6584
68	-0.2486
69	-1.0797
70	-0.8396
71	1.4395
72	0.0835
73	-0.5004
74	1.1735
75	-0.0829
76	-0.4146
77	-0.3331
78	-0.3342
79	-0.0838
80	0.7550
81	-0.6661
82	0.5868
83	0.0833
84	0.4996
85	1.1599
86	0.4095
87	0.0000
88	0.1631
89	0.0000
90	-0.4886
Answer  
There is no answer at this time.

Comments  
Subject: Re: Using Excel to predict future results using past data
From: owejessen-ga on 06 Apr 2006 09:38 PDT
 
I guess no sane person would do this in excel, because it would
involve complicated manipulations with matrices. For an
open-source-solution have a look at r-project.org.
Subject: Re: Using Excel to predict future results using past data
From: hedgie-ga on 06 Apr 2006 19:02 PDT
 
The question is also seriously underpriced.
Calculation requres naking some assumptions. 
It would help to describe what kind of returns are involved.
Subject: Re: Using Excel to predict future results using past data
From: owejessen-ga on 07 Apr 2006 10:04 PDT
 
Well, I'd be so bold and bet on the probable mean of the prediction
;-) Using 90 ticks to predict a further 90 ticks - that's a job for an
astrologue.
Subject: Re: Using Excel to predict future results using past data
From: redfoxjumps-ga on 08 Apr 2006 01:13 PDT
 
There is an irregular wave in your data. It only breaks +2 a couple times.
It also goes negative a number of times. Seems to have an upward bias.
(Does not break 2 in the negative direction.)

A fortune teller might help with predictions

Looks like stock market price data. Right?
Subject: Re: Using Excel to predict future results using past data
From: daveusa-ga on 08 Apr 2006 02:59 PDT
 
many thanks for the comments, yes it is stock market data. (as you can
tell) stats is not my forte - i thought maybe there was a function on
excel i was unaware of......im trying to get a best estimate of a
companies beta in the next 3 months based on their previous 3 months
data...thats where i was comin from.
Subject: Re: Using Excel to predict future results using past data
From: owejessen-ga on 10 Apr 2006 04:30 PDT
 
Why didn't you say so. Calculating the beta is rather straigtforward:
You plot the returns of the stock against the returns of the index.
Then you add the linear trend, and under options tick "give formula".
The beta is the factor in front of the x (something like y=1.21342x +
.0213). The correct labels of your excel may differ since I use the
German version. NB: Make shure that the index is on the x-axis, else
you have to invert the beta.
There also is an excel-add-in, that alows to do the regression,
something like "analysis functions". HTH
Subject: Re: Using Excel to predict future results using past data
From: daveusa-ga on 11 Apr 2006 06:52 PDT
 
thanks for the comment owejessen - i had already calculated 'current'
betas using regression analysis on excel.  this was using historical
share price data.  i need to find the best estimate of beta in three
months time...so a way of estimating future returns is required....
Subject: Re: Using Excel to predict future results using past data
From: owejessen-ga on 12 Apr 2006 02:42 PDT
 
If you want to predict 90 ticks and only have a history of 90 ticks,
any results will be meaningless. So, you might as well use the
historical beta as a predictor for the future beta. You might try to
get a longer series and aggregate them to monthly returns. Then you'd
only have to predict three points and may calculte the beta for these.
Subject: Re: Using Excel to predict future results using past data
From: daveusa-ga on 15 Apr 2006 01:23 PDT
 
thanks again owejessen for your comments, things are clearer to me and
i now realise the question is not really relevant

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy