Excel/access question

[thiedeni19]

Hello

I need a help manipulating data on a spreadsheet. The spreadsheets columns are buy or sell, ticker, price, trade date, execution time. The rows are filled in with varying trade information, with either a buy or sell. I'm trying to identify all trades that took place on the same day and time that have the same ticker, but have different trade type (sell or buy), for instance, I'm interested in pulling a list that shows all the tickers of positions that had buy and sells on the same day at the same time, and I don't care about the rest of the rows. I am familiar with Excel or access, so whichever one would be easier, desperate need of help getting rid of this headache!!
Thanks!

 

[alansidman]

In Access, I am envisioning a Group By Query.

Maybe something like this where the tablename is Ticker

SELECT Ticker.tDate, Ticker.tTime, Ticker.Ticker, Ticker.BuySell, Ticker.Price
FROM Ticker
GROUP BY Ticker.tDate, Ticker.tTime, Ticker.Ticker, Ticker.BuySell, Ticker.Price;

 

[Fazza]

In Excel or Access,

SELECT D.BuySell, D.Ticker, D.Price, D.TradeDate, D.ExecutionTime
FROM YourTable D, (SELECT B.Ticker, B.TradeDate, B.ExecutionTime
FROM (SELECT DISTINCT A.BuySell, A.Ticker, A.TradeDate, A.ExecutionTime
FROM YourTable A) B
GROUP BY B.Ticker, B.TradeDate, B.ExecutionTime
HAVING (Count(*)>1)) C
WHERE C.Ticker = D.Ticker AND C.TradeDate = D.TradeDate AND C.ExecutionTime = D.ExecutionTime

 

[thiedeni19]

In Excel or Access,

SELECT D.BuySell, D.Ticker, D.Price, D.TradeDate, D.ExecutionTime
FROM YourTable D, (SELECT B.Ticker, B.TradeDate, B.ExecutionTime
FROM (SELECT DISTINCT A.BuySell, A.Ticker, A.TradeDate, A.ExecutionTime
FROM YourTable A) B
GROUP BY B.Ticker, B.TradeDate, B.ExecutionTime
HAVING (Count(*)>1)) C
WHERE C.Ticker = D.Ticker AND C.TradeDate = D.TradeDate AND C.ExecutionTime = D.ExecutionTime

When I try this, it's asking to 'Enter Parameter Value' for A.Trade Date, what would I enter for that?

 

[Fazza]

I think the fix is simply to edit the SQL to have all field names identical in both the source table & the SQL.

For example, field name [TradeDate] has no space in the SQL. So should be the same in the source table. Or,
if there is to be a space in the field name in the source table, change the SQL to have a space, viz [Trade Date]

OK?