• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to know the latest received price

D

dsnath

New Member
How to get the following extract from the data

[pre]
Code: 
Security Name,	Price type,	Portfolio,	Price date,	Price, Received on

DATA
Security,		Price type,	Portfolio,	Price date,	Price,		Received on
ACC Ltd	,		Estimate,	ABC,		31/05/2012,	 1,300.00 	08/06/2012
ACC Ltd Sub,		Estimate,	ABC,		31/05/2012,	 262.00, 	08/06/2012
GAIL (India) Ltd,	Estimate,	DEF,		31/05/2012,	 358.00, 	08/06/2012
ACC Ltd,		Estimate,	ABC,		01/06/2012,	 1,302.85,	09/06/2012
ACC Ltd Sub,		Estimate,	ABC,		01/06/2012,	 250.00,	09/06/2012
GAIL (India) Ltd,	Estimate,	DEF,		01/06/2012,	 340.00, 	09/06/2012
ACC Ltd,		Estimate,	ABC,		05/06/2012,	 1,303.00,	13/06/2012
ACC Ltd Sub,		Estimate,	ABC,		05/06/2012,	 275.00, 	13/06/2012
GAIL (India) Ltd,	Estimate,	DEF,		05/06/2012,	 342.00, 	13/06/2012
ACC Ltd,		Estimate,	ABC,		06/06/2012,	 1,305.00, 	14/06/2012
ACC Ltd Sub,		Estimate,	ABC,		06/06/2012,	 260.00, 	14/06/2012
GAIL (India) Ltd,	Estimate,	DEF,		06/06/2012,	 364.00, 	14/06/2012
ACC Ltd,		final,		ABC,		31/05/2012,	 1,285.00, 	15/06/2012
ACC Ltd Sub,		final,		ABC,		31/05/2012,	 284.00, 	15/06/2012
GAIL (India) Ltd,	final,		DEF,		31/05/2012,	 358.00, 	15/06/2012
ACC Ltd,		Estimate,	ABC,		30/06/2012,	 1,275.00, 	08/07/2012
ACC Ltd Sub,		Estimate,	ABC,		30/06/2012,	 281.00, 	08/07/2012
GAIL (India) Ltd,	Estimate,	DEF,		30/06/2012,	 365.00, 	08/07/2012
ACC Ltd,		final,		ABC,		30/06/2012,	 1,290.00, 	15/07/2012
ACC Ltd Sub,		final,		ABC,		30/06/2012,	 279.00, 	15/07/2012
GAIL (India) Ltd,	final,		DEF,		30/06/2012,	 360.00, 	15/07/2012
ACC Ltd,		Estimate,	ABC,		31/07/2012,	 1,325.00, 	08/08/2012
ACC Ltd Sub,		Estimate,	ABC,		31/07/2012,	 265.00, 	08/08/2012
GAIL (India) Ltd,	Estimate,	DEF,		31/07/2012,	 359.00, 	08/08/2012
ACC Ltd,		final,		ABC,		31/07/2012,	 1,335.00, 	15/08/2012
ACC Ltd Sub,		final,		ABC,		31/07/2012,	 268.00, 	15/08/2012
GAIL (India) Ltd,	final,		DEF,		31/07/2012,	 354.00, 	15/08/2012
ACC Ltd,		Estimate,	ABC,		10/08/2012,	 1,345.00, 	18/08/2012
ACC Ltd Sub,		Estimate,	ABC,		10/08/2012,	 285.00, 	18/08/2012
GAIL (India) Ltd,	Estimate,	DEF,		10/08/2012,	 360.00, 	18/08/2012
[/pre]
 
Hi DSnath,


Just use text to coloum and use delimited option >>> next >>>> select "'" and next.


However your first data line do not contain "'" in between price and received on. Take care of it.


Regards,
 
The latest price might also be found using a sort of MAX-IF function. Something like:

=MAX(IF((CritRange1=Crit1)*(CritRange2=Crit2),PriceRange))

eg.

=MAX(IF((LEFT(A2:A20,3)="ACC")*(D2:D20<=TODAY()),E2:E20))


Confirm this formula using Ctrl+Shift+Enter, not just Enter.
 
Oops, just saw that I wrote formula to get largest price, not latest price.


=INDEX(E2:E31,MAX(IF(B2:B31="estimate",ROW(B2:B31))))


Note that the IF function is only needed if you have criteria that you need to meet, such as "latest price for estimates", or "latest price for ABC", etc.
 
Don't clothe shabby,isabel marant sneakers, which ought be by a volume of along least 20 liters 5 There are two modish ways apt propagate the Siamese Fighting-fish- among an aquarium specially set as the couple Global Pet Expo is the caress industry's largest annual commerce show. I've already pedestal an intense green shirt that aspiration shortly be chapter of my closet Further,isabel marant sneakers,blouses
Chanel,isabel marant sneaker,for there namely quite a lot that works on after the shade namely you might not be familiar with Environmentally friendly biodegradable ampules are ideal as eco minded jobbers and consumers identical. Related articles：this duration afresh the designers are all set apt establish the magic afresh with the draped structures you have to be aware namely your clothes supplies should be of the latest trend.cashtoday. Zaibatt Zaki is one author and excellent fiscal advisor of cash today.

Related articles：

 
Hi Luke & KPJSWT....A lot of thanks for your time and efforts to solve my problem.


Actually the extract should get the following table:

[pre]
Code: 
Security'	 Price type   Portfolio	       Price date	Price	Received on
ACC Ltd	         Estimate	ABC	        10/08/2012	1345	18/08/2012
ACC Ltd Sub	 Estimate	ABC	        10/08/2012	285	18/08/2012
GAIL (India) Ltd Estimate	DEF	        10/08/2012	360	18/08/2012
[/pre]
The final table should show the latest price of the securities, with the price date and what type (estimate or final) and when the price was received from the administrator/fund manager.
 
Hoping to get an easy solution, might it be possible to setup an Advanced Filter?

http://www.contextures.com/xladvfilter01.html


You could setup the criteria to eitehr have the user manually enter last date, or use the MAX function to find latest date, and then the filter could quickly extract all the related records.
 
You must log in or register to reply here.
Back
Top