Moving Average projection?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wild Nerd - 20 Mar 2006 17:36 GMT

I have a column of numbers in which I created 2 different Moving Averag
time frames.

Now I want to know:

How to write the formula:

What number is needed today for the moving averages to cross eac
other? For instance, if a shorter moving average is 108 and a longe
one is 123, What number is needed today for the shorter average to b
larger than the longer average? Keep in mind, I have to drop th
oldest time frame price

Reply to this Message

Richard Buttrey - 20 Mar 2006 18:19 GMT

>I have a column of numbers in which I created 2 different Moving Average
>time frames.
[quoted text clipped - 8 lines]
>larger than the longer average? Keep in mind, I have to drop the
>oldest time frame price.

Could you post some example data to help better understand your
requirement?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Reply to this Message

Wild Nerd - 20 Mar 2006 19:23 GMT

576.50    576.3750 2-Day M Avg.    Less Than
576.38    579.0000    3-Day M Avg.    Less Than
581.19    581.8500    5-Day M Avg.    Less Than
583.08    583.7500    7-Day M Avg.    Less Than
587.89    589.2292    12-Day M Avg.    Greater Than
589.63    589.4583    18-Day M Avg.    Greater Than
593.35    593.4667    30-Day M Avg.    Greater Than
592.78    592.3313    40-Day M Avg.    Greater Than


1    576.50
2    576.25
3    584.25
4    587.75
5    584.50
6    589.25
7    587.75
8    584.00
9    590.00
10    600.50
11    606.00
12    604.00
13    591.00
14    594.00
15    593.00
16    590.25
17    584.75
18    586.50
19    601.00
20    614.50
21    604.25
22    599.00
23    596.00
24    597.75
25    596.00
26    601.75
27    591.25
28    588.00
29    607.50
30    596.75
31    592.25
32    606.75
33    609.50
34    599.75
35    584.50
36    578.00
37    581.00
38    585.00
39    577.75
40    574.7

Reply to this Message

Richard Buttrey - 20 Mar 2006 20:11 GMT

I assume A1:A40 is your data. How do the numbers at the top relate to
the data? i.e. what are the 8 rows and how do they relate to the data
rows?

What for instance do the two summary columns of numbers mean and what
are you trying to achieve in columns C & D

Rgds

>576.50    576.3750 2-Day M Avg.    Less Than
>576.38    579.0000    3-Day M Avg.    Less Than
[quoted text clipped - 46 lines]
>39    577.75
>40    574.75

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Reply to this Message

Wild Nerd - 20 Mar 2006 20:31 GMT

Thanks Richard for taking the time to address my problem.

If A1:A40 are my dates. A1 is yesterday and A40 is 40 business day
ago.
and B1:B40 are the closing prices for those dates.

A 2-day Average is simply =average(B1:B2), a 5-day is =Average(B1:B5)

I am a trader who wants to be long if the shorter average is above th
longer average and short if the shorter average is below the longe
average. Let's say I currently own a stock. I want my Excel workshee
to tell me during the current day, what price needs to trade today t
put the short avg below the long avg? Eventually, that day's dat
will be placed into cell B1 after B1-B40 is Ctrl C, arrow down, CTR
V'd

Reply to this Message

Richard Buttrey - 21 Mar 2006 00:42 GMT

>Thanks Richard for taking the time to address my problem.
>
[quoted text clipped - 11 lines]
>will be placed into cell B1 after B1-B40 is Ctrl C, arrow down, CTRL
>V'd.

OK, Thanks,

I just need to understand what you mean by shorter average and longer
average - in terms of the data in your original post. I'm also not
quite clear how your data results in the Less Than and Greater Than
results your OP suggests.

e.g. in the first column for the 2 day moving average (row 1 576.5 -
what are these values incidentally, how are they calculated?), the
moving avg of 576.375 is less than the number in the first column,
however on the second row, the 579 3 day moving avg is more than the
576.38 in the first column. Yet your OP marks these both as 'Less
Than'.

Your OP talked about the moving averages 'crossing each other. Could
you just expand on that please, again with ref to your original data.

I think the Solver Add-in is probably needed for a solution here, but
I just need to fully understand your data first.

Sorry for being a bit wooden about this.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Reply to this Message

Wild Nerd - 21 Mar 2006 14:55 GMT

Richard,
In the OP, the price 576.5 is the closing price for Soybeans at th
Chicago Board of Trade. I currently plug in these prices(column AE
daily.

These are the actual formulas in the spreadsheet. Does this mak
better sense?

=AVERAGE(AE41:AE42)-- (Shorter Avg(2-Day))
=AVERAGE(AE41:AE43)-- (Shorter Avg(3-Day))
=AVERAGE(AE41:AE45)-- (Shorter Avg(5-Day))
=AVERAGE(AE41:AE47)-- (Shorter Avg(7-Day))
=AVERAGE(AE41:AE52)-- (Longer Avg(12-Day))
=AVERAGE(AE41:AE58)-- (Longer Avg(18-Day))
=AVERAGE(AE41:AE70)-- (Longer Avg(30-Day))
=AVERAGE(AE41:AE80)-- (Longer Avg(40-Day))

AD    AE
1    576.5
=+AD41+1    576.25
=+AD42+1    584.25
=+AD43+1    587.75
=+AD44+1    584.5
=+AD45+1    589.25
=+AD46+1    587.75
=+AD47+1    584
=+AD48+1    590
=+AD49+1    600.5
=+AD50+1    606
=+AD51+1    604
=+AD52+1    591
=+AD53+1    594
=+AD54+1    593
=+AD55+1    590.25
=+AD56+1    584.75
=+AD57+1    586.5
=+AD58+1    601
=+AD59+1    614.5
=+AD60+1    604.25
=+AD61+1    599
=+AD62+1    596
=+AD63+1    597.75
=+AD64+1    596
=+AD65+1    601.75
=+AD66+1    591.25
=+AD67+1    588
=+AD68+1    607.5
=+AD69+1    596.75
=+AD70+1    592.25
=+AD71+1    606.75
=+AD72+1    609.5
=+AD73+1    599.75
=+AD74+1    584.5
=+AD75+1    578
=+AD76+1    581
=+AD77+1    585
=+AD78+1    577.75
=+AD79+1    574.75
=+AD80+1    574.75
=+AD81+1    580.7

Reply to this Message