You helped me with a union sql sometime ago in A97. The query seemed to work(reasonable values). After checking today The values are not correct, could you look at it again and let me know where the conversion or my fat fingers have corrupted the values
Code: Select all
SELECT *
FROM quOpenPositionsLong
UNION SELECT *
FROM quOpenPositionsShort;
Code: Select all
SELECT Trades.Symbol, quSumOpenPositions.[Sum of Symbol], Sum(Trades.Amount) AS [Open Cost], Sum(Abs(Nz([amount])))/Sum(Nz([qtyfilled])) AS [Avg Cost]
FROM quSumOpenPositions LEFT JOIN (Trades LEFT JOIN quLatestSell ON Trades.Symbol = quLatestSell.Symbol) ON quSumOpenPositions.Symbol = Trades.Symbol
WHERE (((Trades.Filled)>#8/6/2015#) AND ((Trades.Type)="Buy"))
GROUP BY Trades.Symbol, quSumOpenPositions.[Sum of Symbol]
HAVING (((quSumOpenPositions.[Sum of Symbol])>0))
ORDER BY Trades.Symbol;
Code: Select all
SELECT Trades.Symbol, quSumOpenPositions.[Sum of Symbol], Sum(Trades.Amount) AS [Open Cost], Abs(Sum((Nz([amount])))/Sum(Nz([qtyfilled]))) AS [Avg Cost]
FROM quSumOpenPositions LEFT JOIN (Trades LEFT JOIN quShortOpen ON (Trades.Filled = quShortOpen.LastSell) AND (Trades.Symbol = quShortOpen.Symbol)) ON quSumOpenPositions.Symbol = Trades.Symbol
WHERE (((Trades.Filled)>#8/6/2015#) AND ((Trades.Type) Like "Sell*"))
GROUP BY Trades.Symbol, quSumOpenPositions.[Sum of Symbol]
HAVING (((quSumOpenPositions.[Sum of Symbol])<0))
ORDER BY Trades.Symbol;
Notice the query image for OKS -43935 and of the Excel output from the Db -11285. The value of -11285 is not exactly correct, but is much closer to being correct, than the -43935. The percentage of -11285 would be approximately 10%, much closer than the 19.48% from quOpenPositionsFirst.
Would you have a look when you have sufficient time to analyze my errors with hopefully a complete enough description why the cost percentage and sum seem to be incorrect?