Fetching data based on conditions

[MoneyPlanters]

Please have a look at the image.
Col A contains names of stock exchanges.
Col B contains dates.
Col C, D, E and F contain data that we shall be using. Col G and H are not required.

Every day one such file shall be imported from the internet and the newer file will always keep adding (appending) data to the existing one. For example if the screenshot is considered to be taken from a file that was downloaded today, a file downloaded 7 days later will contain more data than this one plus whatever this one has.

Now we need to achieve the following :

If col A is 'CAC40' then pick the OHLC (stands for Open, High, Low, Close; present in Col C through F) values that correspond to the latest date available and paste them in L824, M824, N824, O824.

Similarly, If col A is 'COMP' then pick the OHLC (stands for Open, High, Low, Close; present in Col C through F) values that correspond to the latest date available and paste them in L825, M825, N825, O825.

And so on.. till all the markets are covered. There are 15 of them.

How can we achieve this ? Kindly help.
Thank you.

 

[T. Valko]

Never mind!

 

[MoneyPlanters]

I know :P

I have edited the post and linked the image. Please have a look.

Thanks.

 

[T. Valko]

I know :P

I have edited the post and linked the image. Please have a look.

Thanks.

I did but the data in the image is too small for me to read!

 

[MoneyPlanters]

You can click on the image and that should open a large copy of the image.

 

[njimack]

You should be able to use the example below.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Ticker	Date/Time	Open	High	Low	Close	*	*	Open	High	Low	Close
2	CAC40	01-Jan	2688	4140	3788	1774	*	CAC40	13632	10900	16202	16463
3	CAC40	02-Jan	3226	471	1800	3622	*	COMP	19092	12431	16222	20508
4	CAC40	03-Jan	737	615	82	877	*	*	*	*	*	*
5	CAC40	04-Jan	1770	1848	1584	481	*	*	*	*	*	*
6	CAC40	05-Jan	1210	535	3391	2992	*	*	*	*	*	*
7	COMP	01-Jan	4676	1749	198	3599	*	*	*	*	*	*
8	COMP	02-Jan	1898	2392	4968	3320	*	*	*	*	*	*
9	COMP	03-Jan	2949	1618	2966	878	*	*	*	*	*	*
10	COMP	04-Jan	2777	625	367	2269	*	*	*	*	*	*
11	COMP	05-Jan	2791	2756	2166	3725	*	*	*	*	*	*
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[T. Valko]

You can click on the image and that should open a large copy of the image.

Ok...

Enter this formula in L824:

=INDEX(C$811:C$1000,MATCH($K824,$A$811:$A$1000,0)+COUNTIF($A$811:$A$1000,$K824)-1)

Copy across to O824 then down as needed.

Adjust for a reasonable end of range, row 1000.

 

[MoneyPlanters]

Thank you so much njimack and T. Valko.

Something just struck me.. that is.. this file will get progressively heavier, since everyday, new data will be appended to it and will make it more complicated. What we could do is, delete all data which are more than a year old and then run the above formulae.. that should make it more easier.

Is that doable ? Do we need any modifications in the formulae for this ?

 

[T. Valko]

Thank you so much njimack and T. Valko.

Something just struck me.. that is.. this file will get progressively heavier, since everyday, new data will be appended to it and will make it more complicated. What we could do is, delete all data which are more than a year old and then run the above formulae.. that should make it more easier.

Is that doable ? Do we need any modifications in the formulae for this ?

That would certainly help make the formulas more efficient since the size of the calculated range will be reduced.

If you simply delete the data then you'll have to update the range references in the formulas.

 

[MoneyPlanters]

cant it be automated ?