Summation of last (DD) days
 

i am tring to create a simple Nasdaq Breadth rati
where the sum of the advancing volume for the past 8 days(including today
is divided by the declining volume for the past 8 days (includiny today
for anyone interested in the market i would buy when ADR=1.5 and sell when ADR=0.8
this would be updated daily from yahoo
DATE AV DATE D
20040331 674931876 20040331 111957091
20040401 1322679760 20040401 44943118

i have the formulae but need to transfer into excel languag
Data
AV = Nasdaq Advancing Volum
DV = Nasdaq Declining Volum
Formulae
SUM_AV = Summation of last (8) days (including current day) of A
SUM_DV = Summation of last (8) days (including current day) of D
ADR = SUM_UV / SUM_D

TIA, cirius3000

Summation of last (DD) days
 

Hi
if your dates are stored as real dates (and not as text values) in
column a and C use the following formulas
=SUMIF(A1:A1000,"=" & TODAY()-7,B1:B1000)

and
=SUMIF(C1:C1000,"=" & TODAY()-7,D1:d1000)


--
Regards
Frank Kabel
Frankfurt, Germany


cirius3000 wrote:
i am tring to create a simple Nasdaq Breadth ratio
where the sum of the advancing volume for the past 8 days(including
today)
is divided by the declining volume for the past 8 days (includiny
today)
for anyone interested in the market i would buy when ADR=1.5 and sell
when ADR=0.8 this would be updated daily from yahoo
DATE AV DATE DV
20040331 674931876 20040331 1119570915
20040401 1322679760 20040401 449431185

i have the formulae but need to transfer into excel language
Data:
AV = Nasdaq Advancing Volume
DV = Nasdaq Declining Volume
Formulae:
SUM_AV = Summation of last (8) days (including current day) of AV
SUM_DV = Summation of last (8) days (including current day) of DV
ADR = SUM_UV / SUM_DV

TIA, cirius3000

Summation of last (DD) days
 

A bit crude but fairly simple to implement:

Let's say your AV row ends at B19. In cell B20, place the following formula
for the total:

=SUM(OFFSET(B20,-1,0):OFFSET(B20,-8,0))

This will keep a running total of the last 8 rows. Every day, you can insert
a new row above the total and the formula will adjust itself accordingly.

You can do the same thing with the DV column and calculate the ratio.

--

Vasant



"cirius3000" wrote in message
...
i am tring to create a simple Nasdaq Breadth ratio
where the sum of the advancing volume for the past 8 days(including today)
is divided by the declining volume for the past 8 days (includiny today)
for anyone interested in the market i would buy when ADR=1.5 and sell when
ADR=0.8
this would be updated daily from yahoo
DATE AV DATE DV
20040331 674931876 20040331 1119570915
20040401 1322679760 20040401 449431185

i have the formulae but need to transfer into excel language
Data:
AV = Nasdaq Advancing Volume
DV = Nasdaq Declining Volume
Formulae:
SUM_AV = Summation of last (8) days (including current day) of AV
SUM_DV = Summation of last (8) days (including current day) of DV
ADR = SUM_UV / SUM_DV

TIA, cirius3000

Summation of last (DD) days
 

Hi Frank:

This may not work if weekends and holidays are involved, which probably
would be the case with stock trading.

Regards,

Vasant.

"Frank Kabel" wrote in message
...
Hi
if your dates are stored as real dates (and not as text values) in
column a and C use the following formulas
=SUMIF(A1:A1000,"=" & TODAY()-7,B1:B1000)

and
=SUMIF(C1:C1000,"=" & TODAY()-7,D1:d1000)


--
Regards
Frank Kabel
Frankfurt, Germany


cirius3000 wrote:
i am tring to create a simple Nasdaq Breadth ratio
where the sum of the advancing volume for the past 8 days(including
today)
is divided by the declining volume for the past 8 days (includiny
today)
for anyone interested in the market i would buy when ADR=1.5 and sell
when ADR=0.8 this would be updated daily from yahoo
DATE AV DATE DV
20040331 674931876 20040331 1119570915
20040401 1322679760 20040401 449431185

i have the formulae but need to transfer into excel language
Data:
AV = Nasdaq Advancing Volume
DV = Nasdaq Declining Volume
Formulae:
SUM_AV = Summation of last (8) days (including current day) of AV
SUM_DV = Summation of last (8) days (including current day) of DV
ADR = SUM_UV / SUM_DV

TIA, cirius3000

Summation of last (DD) days
 

Hi
good point. Another way would be to use the following function:
=SUMPRODUCT(--(A1:A1000=WORKDAY(TODAY(),-7,list_of_holidays)),B1:B1000
)

and
=SUMPRODUCT(--(C1:C1000=WORKDAY(TODAY(),-7,list_of_holidays)),D1:D1000
)


--
Regards
Frank Kabel
Frankfurt, Germany


Vasant Nanavati wrote:
Hi Frank:

This may not work if weekends and holidays are involved, which
probably would be the case with stock trading.

Regards,

Vasant.

"Frank Kabel" wrote in message
...
Hi
if your dates are stored as real dates (and not as text values) in
column a and C use the following formulas
=SUMIF(A1:A1000,"=" & TODAY()-7,B1:B1000)

and
=SUMIF(C1:C1000,"=" & TODAY()-7,D1:d1000)


--
Regards
Frank Kabel
Frankfurt, Germany


cirius3000 wrote:
i am tring to create a simple Nasdaq Breadth ratio
where the sum of the advancing volume for the past 8 days(including
today)
is divided by the declining volume for the past 8 days (includiny
today)
for anyone interested in the market i would buy when ADR=1.5 and
sell when ADR=0.8 this would be updated daily from yahoo
DATE AV DATE DV
20040331 674931876 20040331 1119570915
20040401 1322679760 20040401 449431185

i have the formulae but need to transfer into excel language
Data:
AV = Nasdaq Advancing Volume
DV = Nasdaq Declining Volume
Formulae:
SUM_AV = Summation of last (8) days (including current day) of AV
SUM_DV = Summation of last (8) days (including current day) of DV
ADR = SUM_UV / SUM_DV

TIA, cirius3000