Page 1 of 1
Stock Transaction List
Posted: Fri Jun 14, 2024 8:12 am
by spillaip
I am trying to write a Stock Transactions report between two dates, need help with the query. The format is:
Date of Transaction
Type of transaction
Stock
Qty
Price
Cost (Qty*Price)
Current Price
Current Value
Help is greatly appreciated.
Re: Stock Transaction List
Posted: Thu Jun 27, 2024 9:38 am
by Fanntiago
I think you can add the buy and sell volume during the day
Re: Stock Transaction List
Posted: Mon Jul 08, 2024 5:10 am
by spillaip
I have already input the stock transactions. The need is to get a query to get list of stock transactions.
Re: Stock Transaction List
Posted: Mon Jul 08, 2024 7:14 pm
by Renato
No developers are currently active.
Users who are active in the forum will probably not have such a good knowledge of SQL to be able to fulfil such requests.
MMEX is a classic open source project and there is no entitlement to support. The forum here is a user forum, which means that users help each other.
I have tried to document further explanations about Open Source here:
viewtopic.php?t=11132
Re: Stock Transaction List
Posted: Tue Aug 06, 2024 5:35 pm
by nstein
I would start with something like this:
Code: Select all
SELECT
c.TRANSDATE AS 'Date',
CASE
WHEN c.TRANSCODE = "Withdrawal" THEN 'Buy'
ELSE 'Sell'
END AS 'Type',
s.STOCKNAME AS 'Name',
s.SYMBOL AS 'Symbol',
i.SHARENUMBER AS 'Qty',
i.SHAREPRICE AS 'Price',
ROUND(i.SHARENUMBER * i.SHAREPRICE, 4) AS 'Amount',
ROUND(h.VALUE,4) AS 'Current Price',
ROUND(i.SHARENUMBER * h.VALUE ,4) AS 'Current Amount'
FROM CHECKINGACCOUNT_V1 AS c
JOIN TRANSLINK_V1 AS l ON l.CHECKINGACCOUNTID = c.TRANSID
JOIN STOCK_V1 AS s ON s.STOCKID = l.LINKRECORDID
JOIN SHAREINFO_V1 AS i ON i.CHECKINGACCOUNTID = c.TRANSID
JOIN (SELECT SYMBOL, VALUE FROM STOCKHISTORY_V1 AS sh
WHERE DATE = (
SELECT MAX(DATE)
FROM STOCKHISTORY_V1
WHERE SYMBOL = sh.SYMBOL))
AS h ON h.SYMBOL = s.SYMBOL
WHERE l.LINKTYPE = "Stock"
ORDER BY c.TRANSDATE