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






























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:
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8668102001[/TD]
[TD]2001[/TD]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8668102002[/TD]
[TD]2002[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8668102003[/TD]
[TD]2003[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8668102004[/TD]
[TD]2004[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]8668102005[/TD]
[TD]2005[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]8668102006[/TD]
[TD]2006[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1941622001[/TD]
[TD]2001[/TD]
[TD]115[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1941622002[/TD]
[TD]2002[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1941622003[/TD]
[TD]2003[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1941622004[/TD]
[TD]2004[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1941622005[/TD]
[TD]2005[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1941622006[/TD]
[TD]2006[/TD]
[TD][/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]8645252001[/TD]
[TD]2001[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]8645252002 (etc..)[/TD]
[TD]2002 (etc..)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]























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...