Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mcarrington
 
Posts: n/a
Default identify first date a record appears


Hi, I have four columns of data and would like to identify:
1-what is the first date a symbol was bought or sold
2-On what dates did the number of shares for that symbol = 0 (like if
we bought 1000 and sold a total of 1000 a few days later)
Does anybody have any thoughts on this? I am at a loss and would love
your input!

Date Symbol Action Shares
12/1/2005 BBB Sell -1200
12/1/2005 FFF Sell -1000
11/30/2005 BBB Buy 400
11/30/2005 AAA Sell -200
11/29/2005 GGG Sell -500
11/29/2005 CCC Sell -1000
11/28/2005 DDD Buy 700
11/28/2005 BBB Sell -200
11/25/2005 GGG Sell -500
11/23/2005 GGG Buy 1000
11/23/2005 FFF Buy 1000
11/23/2005 EEE Buy 1000
11/23/2005 DDD Sell -1000
11/22/2005 CCC Buy 1000
11/22/2005 BBB Buy 1000
11/22/2005 AAA Sell -1000


--
mcarrington
------------------------------------------------------------------------
mcarrington's Profile: http://www.excelforum.com/member.php...o&userid=21641
View this thread: http://www.excelforum.com/showthread...hreadid=492832

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default identify first date a record appears

Let A1:D17 house the sample you provided, with A1:D1 housing the labels
Date, Symbol, etc.

In E2 enter & copy down:

=SUMIF($B$2:B2,B2,$D$2:D2)

F2: FFF

which is a symbol of interest.

G1: Buy

H1: Sell

I1: 0

G2:

=MIN(IF($B$2:$B$17=$F2,IF($C$2:$C$17=G$1,$A$2:$A$1 7)))

which you need to confirm with control+shift+enter then copy across to H2.

I2:

=INDEX($A$2:$A$17,MATCH(1,($B$2:$B$17=F2)*($E$2:$E $17=I$1),0))

which you also need to confirm with control+shift+enter.

Hope the latter is sufficient regarding your 2nd query.

mcarrington wrote:
Hi, I have four columns of data and would like to identify:
1-what is the first date a symbol was bought or sold
2-On what dates did the number of shares for that symbol = 0 (like if
we bought 1000 and sold a total of 1000 a few days later)
Does anybody have any thoughts on this? I am at a loss and would love
your input!

Date Symbol Action Shares
12/1/2005 BBB Sell -1200
12/1/2005 FFF Sell -1000
11/30/2005 BBB Buy 400
11/30/2005 AAA Sell -200
11/29/2005 GGG Sell -500
11/29/2005 CCC Sell -1000
11/28/2005 DDD Buy 700
11/28/2005 BBB Sell -200
11/25/2005 GGG Sell -500
11/23/2005 GGG Buy 1000
11/23/2005 FFF Buy 1000
11/23/2005 EEE Buy 1000
11/23/2005 DDD Sell -1000
11/22/2005 CCC Buy 1000
11/22/2005 BBB Buy 1000
11/22/2005 AAA Sell -1000


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mcarrington
 
Posts: n/a
Default identify first date a record appears


Thanks Aladin, the formulas seem to be on the right track. However, I
am having a couple of issues. The only date that is pulled up is the
first one on the list. The formula seems ot be just taking the min
date on the list, instead of the first date of buy or sell. Also, does
confirming by using control+shift+enter do? To my knowledge I've never
used that command and it doesn't appear to do anything when I tried it.
Thanks again for you response.

Megan


--
mcarrington
------------------------------------------------------------------------
mcarrington's Profile: http://www.excelforum.com/member.php...o&userid=21641
View this thread: http://www.excelforum.com/showthread...hreadid=492832

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default identify first date a record appears


You must press the control, shift, and enter keys at the same time.

Also, lookup "array formulas" in Excel's Help for an intro.


mcarrington wrote:
Thanks Aladin, the formulas seem to be on the right track. However, I
am having a couple of issues. The only date that is pulled up is the
first one on the list. The formula seems ot be just taking the min
date on the list, instead of the first date of buy or sell. Also, does
confirming by using control+shift+enter do? To my knowledge I've never
used that command and it doesn't appear to do anything when I tried it.
Thanks again for you response.

Megan


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
NETWORKDAYS - Multiple Date Selection Annabelle Excel Discussion (Misc queries) 3 October 4th 05 07:04 PM
Can a date be used for conditional formatting? Stevie P Excel Worksheet Functions 2 September 27th 05 09:42 PM
How do I calculate if a date is in a certain time frame? Pe66les Excel Worksheet Functions 19 August 27th 05 11:07 PM
date and time ladimples247 New Users to Excel 2 February 16th 05 09:52 PM
Problem with Date format from VBA code twig Excel Discussion (Misc queries) 3 December 7th 04 07:01 PM


All times are GMT +1. The time now is 01:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"