Is it possible by Excel ?

A

andrey_tech

The following is the daily data of stock. The question is : Can exce
convert this daily data into 2 daily, 3 daily and 5 daily (weekly) ?


Example:


This is orginal daily data
Date Open High Low Close

08/09/2005 13.4600 13.8500 13.3000 13.7400
08/10/2005 13.7700 13.8600 13.5500 13.6000
08/11/2005 13.5100 13.7000 13.3200 13.6200
08/12/2005 13.6800 13.7000 13.3600 13.6000
08/15/2005 13.3400 13.8000 13.1300 13.7900
08/16/2005 13.8000 14.4000 13.6100 13.7900
08/17/2005 13.6800 13.9300 13.3200 13.4300
08/18/2005 13.2100 13.5300 12.8200 13.1500
08/19/2005 13.1000 13.1000 12.3500 12.5700
08/22/2005 12.5000 12.9900 12.3200 12.7200
08/23/2005 12.7500 13.1400 12.7100 13.0100
08/24/2005 13.1100 13.4500 12.7800 13.1600
08/25/2005 13.2800 14.4300 13.2800 14.2000


NOW it must be converted into

2 daily as

08/09/2005 13.46 13.86 13.30 13.60
08/11/2005 13.51 13.70 13.32 13.60
08/15/2005 13.34 14.40 13.13 13.79
......... etc

for 3 daily as

08/09/2005 13.46 13.86 13.30 13.62
08/12/2005 13.68 14.40 13.13 13.79
.... etc
 
A

andrey_tech

A B C D E

8/1/2005 27.9 27.92 27.29 27.39
8/2/2005 27.36 27.93 27.39 27.4
8/3/2005 27.4 27.67 27.32 27.58
8/4/2005 27.54 27.57 26.92 27.11
8/5/2005 27 27 26.25 26.65
8/8/2005 26.7 26.79 26.1 26.27
8/9/2005 26.3 26.48 26.08 26.27

The above is the original excel file. The new excel file must be :

A B C D E

8/1/2005 27.9 27.93 27.29 27.4
8/3/2005 27.4 27.67 26.92 27.11
8/5/2005 27 27 26.1 26.27


A: Date
B: Opening price of the stock
C: Highest price on the date
D: Lowest price on the date
E: Closing price of the stock


The above example we turned daily price of stock into 2 daily. How can
excel done by this automatically ?

Regards,
Andrey
 
R

Ron Rosenfeld

The following is the daily data of stock. The question is : Can excel
convert this daily data into 2 daily, 3 daily and 5 daily (weekly) ?


Example:


This is orginal daily data
Date Open High Low Close

08/09/2005 13.4600 13.8500 13.3000 13.7400
08/10/2005 13.7700 13.8600 13.5500 13.6000
08/11/2005 13.5100 13.7000 13.3200 13.6200
08/12/2005 13.6800 13.7000 13.3600 13.6000
08/15/2005 13.3400 13.8000 13.1300 13.7900
08/16/2005 13.8000 14.4000 13.6100 13.7900
08/17/2005 13.6800 13.9300 13.3200 13.4300
08/18/2005 13.2100 13.5300 12.8200 13.1500
08/19/2005 13.1000 13.1000 12.3500 12.5700
08/22/2005 12.5000 12.9900 12.3200 12.7200
08/23/2005 12.7500 13.1400 12.7100 13.0100
08/24/2005 13.1100 13.4500 12.7800 13.1600
08/25/2005 13.2800 14.4300 13.2800 14.2000


NOW it must be converted into

2 daily as

08/09/2005 13.46 13.86 13.30 13.60
08/11/2005 13.51 13.70 13.32 13.60
08/15/2005 13.34 14.40 13.13 13.79
........ etc

for 3 daily as

08/09/2005 13.46 13.86 13.30 13.62
08/12/2005 13.68 14.40 13.13 13.79
... etc.
Click to expand...

Yes, it can

Let us say you have your table of stock prices in A1:En and have named it
DataTbl.

The first column in that table: A:A is named Date

You also have a named field called Span which is how the span of dates for
which you want the new table (e.g. your 2 daily table).

To set up your new table, set up a table with the following columns:

StartDate EndDate Open High Low Close

(You can hide the EndDate column if you wish).

This new table starts with the first date in H3

Enter the following formulas:

StartDate
H3: =A3
H4: =INDEX(Date,MATCH(H3,Date)+Span,0)
Copy/Drag down as far as needed

EndDate
I3: =INDEX(Date,MATCH(H3,Date)+Span-1,0)

Open
J3: =VLOOKUP(H3,DataTbl,2)

High
K3: =MAX(OFFSET($A$1,MATCH($H3,Date),2,Span))

Low
L3: =MIN(OFFSET($A$1,MATCH($H3,Date),3,Span))

Close
M3: =VLOOKUP(I3,DataTbl,5)

Select I3:M3 and copy/drag down as far as needed.

There is no reason why your 2 daily, 3 daily, etc tables could not be on
separate worksheets, and have your Data Table on it's own worksheet too. You'd
just have to alter the references a bit.


--ron
 
A

andrey_tech

Thanks Ron. I will try to use your help with my limited programmin
knowledge. I hope I can done
 
R

Ron Rosenfeld

Thanks Ron. I will try to use your help with my limited programming
knowledge. I hope I can done.
Click to expand...

Just follow along step by step at first; then make the modifications to match
with your particulars.


--ron
 
Top