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

New Member
Joined
Mar 11, 2014
Messages
2
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:
ABCDE
1
=VLOOKUP(A2,L2:N44900,3,FALSE)(company '866810' in year 2001)

<tbody>
</tbody>
1/1/2001128668102001
2
8668102001

<tbody>
</tbody>
1/2/2001118668102001
3
8668102001

<tbody>
</tbody>
1/3/2001138668102001
4(etc-> up to: '8668102006')(up to last trading day of 2006 -> isn't always 12/31/2006)108668102006
51914162001 company '194162' in year 2001)1/1/20011151914162001
6
1941622001

<tbody>
</tbody>
1/2/20011001914162001
7
1941622001

<tbody>
</tbody>
1/3/20011081914162001
8(etc-> up to: '1941622006')(up to last trading day of 2006)1101914162006
98645252001 (company 866810 in year 2001)1/1/200158645252001
1086452520011/2/200178645252001
1186452520011/3/200158645252001
12(etc-> up to: '8645252006')(up to last trading day of 2006)68645252006
135454102001 (company 866810 in year 2001)1/1/2001555454102001
14(etc-> up to: '5454102006')(up to last trading day of 2006)225454102006

<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:
ABCD
18668102001200113
286681020022002
386681020032003
486681020042004
586681020052005
68668102006200610
719416220012001115
819416220022002
919416220032003
1019416220042004
1119416220052005
1219416220062006110
13864525200120017
148645252002 (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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

FlorisL

New Member
Joined
Mar 11, 2014
Messages
2
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,413
Messages
5,571,965
Members
412,429
Latest member
brahmaiah
Top