Hello gang! I've got two question on how to 'find' some values in a specific data file, namely: - the '52-Week High' of the share price (highest valu

[FlorisL]

Hello gang!

I've got two question on how to 'find' some values in a specific data file, namely:
- the '52-Week High' of the share price (highest value of the share in that specific year)
- the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..)

Simplified, my data looks as follows:

-In column 'A' I've a created a line of unique edentifiers (company CUSIP + year -> combined column D and E), in my set this column consists of 405 companies over 6 years of daily data -> aprox 460000 rows
-In column 'B' the date is shown -> its daily trading data
-In column 'C' the daily share price is shown
-In column 'D' the company's CUSIP (edentifier for specific company)
-In column 'E' the year

Sheet 1:

	A	B	C	D	E
1	=VLOOKUP(A2,L2:N44900,3,FALSE)(company '866810' in year 2001) <tbody> </tbody>	1/1/2001	12	866810	2001
2	8668102001 <tbody> </tbody>	1/2/2001	11	866810	2001
3	8668102001 <tbody> </tbody>	1/3/2001	13	866810	2001
4	(etc-> up to: '8668102006')	(up to last trading day of 2006 -> isn't always 12/31/2006)	10	866810	2006
5	1914162001 company '194162' in year 2001)	1/1/2001	115	191416	2001
6	1941622001 <tbody> </tbody>	1/2/2001	100	191416	2001
7	1941622001 <tbody> </tbody>	1/3/2001	108	191416	2001
8	(etc-> up to: '1941622006')	(up to last trading day of 2006)	110	191416	2006
9	8645252001 (company 866810 in year 2001)	1/1/2001	5	864525	2001
10	8645252001	1/2/2001	7	864525	2001
11	8645252001	1/3/2001	5	864525	2001
12	(etc-> up to: '8645252006')	(up to last trading day of 2006)	6	864525	2006
13	5454102001 (company 866810 in year 2001)	1/1/2001	55	545410	2001
14	(etc-> up to: '5454102006')	(up to last trading day of 2006)	22	545410	2006

<tbody>
</tbody>

My final data set has to look something like this:
-In column 'A' the unique edentifiers (company CUSIP + year)
-In column 'B' the specific year
-In column 'C' the '52-Week High' of the share price (highest value of the share in that specific year) -> red numbers from sheet 1
-In column 'D' the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..) -> green numbers from sheet 1
-> off course all the empty cells in column C and D normally contain the missing values

Sheet 2:

	A	B	C	D
1	8668102001	2001	13
2	8668102002	2002
3	8668102003	2003
4	8668102004	2004
5	8668102005	2005
6	8668102006	2006		10
7	1941622001	2001	115
8	1941622002	2002
9	1941622003	2003
10	1941622004	2004
11	1941622005	2005
12	1941622006	2006		110
13	8645252001	2001	7
14	8645252002 (etc..)	2002 (etc..)

<tbody>
</tbody>

The big question is how do I manage to identify the red and green colored values (and link them to 'sheet 2')?

Regarding to the '52-Week High' (red colored in the sheets) I've already tried with the following functions:
- VLOOKUP: When I use this one I only get the most upper value, for example in the case of '8668102001' this would become '12'
- Combined INDEX/MATCH: The same problem as with VLOOKUP
- IF-function:In this case I get the max value of the entire 'C'-column, in the example of sheet 1: this would be '115'

Regarding the value of the share price of the last trading day of a specific year, I couldn't manage to come up with one.


Since I'm already stuck on the same point because of these problems, I really hope someone could help me with these questions !!

Regards,
Floris

 

[FlorisL]

PS. I'm sorry for the lousy layout.. it is my first post and I didn't know the table wouldn't show the inside borders, as well as the abundance of enters in the post...