Individual cost percent

[bknight]

Hans:
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;

quOpenPositionsLong

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;

quOpenPositionsShort

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;

The Db: https://app.box.com/s/vgz77djvdv9bbdujfsvn09xij5zbuhhk" onclick="window.open(this.href);return false;

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?

[HansV]

The sum of Amount for all records with Symbol = OKS and Filled > 08/06/2015 is -11,285.73.
The sum for records with Type = "Buy" is -43,935, and the sum for records whose Type <> "Buy" is 32,649.27. These numbers add up to -11,285.73.
In quOpenPositionsLong, you restrict to Type = "Buy", so the sum of Amount is -43,935.

If this is not what you want, please explain again what you need to return.

[bknight]

Mathematically that is correct and I'm trying to remember why we developed the two queries one short one long then unioned them for a "final" position query. The long query works well if there is only one buy(specific case) multiple buys and sells isn't correct (general case), present condition being displaced by previous "round trips" (buys and sells profit/loss). For example the OKS has +1880 P/L from previous round trips thereby reducing the actual current cost +41.02 down to +35.27 (from spreadsheet V20). The 41.02*320~= 13126 this is approximately 12% of total portfolio. I'm not sure how to replicate that value, but I could live with 35.27*320~=11286(10%)

[HansV]

I'll have to think about that later on - I'll be away from my computer for some hours.

[HansV]

I have been staring at this for some time. It looks like you want to add ALL amounts, not just those with Type = "Buy". I don't see another plausible way to end up at or near -11,286. Correct?