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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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

Forum statistics

Threads
1,171,627
Messages
5,876,522
Members
433,199
Latest member
guerin47

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top