Question

I have 2 databases, TradingDB and Accounts witch are related.
In the TradingDB I have property's:
Clossing Time (date property with date and time)
Profit (Number property with eur format)

I want to make a formula in the Accounts database witch gives me a list of every unique Closing Time (day!) Profit.

So it should sum every entry's profit with the same day (in Closing Time property) and make a list of the profits.

Is this possible?

prop("Trading DB").map(current.prop("⏰CLOSE TIME").formatDate("YYYY-MM-DD")).unique().map(
  lets(
        tradingdays,
        current,
        trades,
        prop("Trading DB").find(
            current.prop("⏰CLOSE TIME").formatDate("YYYY-MM-DD") == tradingdays
            ),
            trades.prop("Profit")
    )
)

I have this so far but this gives only 1 entry of that day and not all entry's summed for each day.

Kind regards
TommyFFX

Comments

lets(
    tradingdays,
    prop("Trading DB").map(current.prop("⏰CLOSE TIME").formatDate("DD-MM-YYYY")).unique(),
    uniquetradingdays,
    prop("Trading DB").map(current.prop("⏰CLOSE TIME").formatDate("DD-MM-YYYY")).unique().length(),
    prop("Trading DB").filter(
                                current.prop("⏰CLOSE TIME").formatDate("DD-MM-YYYY") == tradingdays.at(
                                    uniquetradingdays - uniquetradingdays)).map(
                                        current.prop("Profit")).sum()
    + " ," +
    prop("Trading DB").filter(
                                current.prop("⏰CLOSE TIME").formatDate("DD-MM-YYYY") == tradingdays.at(
                                    uniquetradingdays - uniquetradingdays +1)).map(
                                        current.prop("Profit")).sum()
    + " ," +
    prop("Trading DB").filter(
                                current.prop("⏰CLOSE TIME").formatDate("DD-MM-YYYY") == tradingdays.at(
                                    uniquetradingdays - uniquetradingdays +2)).map(
                                        current.prop("Profit")).sum()
    + " ," +
    prop("Trading DB").filter(
                                current.prop("⏰CLOSE TIME").formatDate("DD-MM-YYYY") == tradingdays.at(
                                    uniquetradingdays - uniquetradingdays +3)).map(
                                        current.prop("Profit")).sum()
    + " ," +
    prop("Trading DB").filter(
                                current.prop("⏰CLOSE TIME").formatDate("DD-MM-YYYY") == tradingdays.at(
                                    uniquetradingdays - uniquetradingdays +4)).map(
                                        current.prop("Profit")).sum()
    + " ," +
    prop("Trading DB").filter(
                                current.prop("⏰CLOSE TIME").formatDate("DD-MM-YYYY") == tradingdays.at(
                                    uniquetradingdays - uniquetradingdays +5)).map(
                                        current.prop("Profit")).sum()
    )

Now I get my list but this not ideal because for every new day I need to make the formule larger, there must be another way..

Kind regards

---

The reason I want to have this formula is for making this consistency formula:

(1 – (absolute value of the most profitable or losing day / absolute result of all trading days)) x 100

So I had this first but it was not right because it's was calculated on each profit and not profit/day

let(
    ConsistencyScore,
        (1-(prop("Trading DB").map(current.prop("Profit")).max() 
                / 
                prop("Trading DB").map(current.prop("Profit").abs()).sum()
        )) * 100,
    ConsistencyScore
)

Answer 1

SOLVED !!!!

lets(
  trades, prop("Trading DB").map(current),
  tradedates, trades.map(current.prop("⏰CLOSE TIME").formatDate("DD-MM-YYYY")),
  uniqueTradingDays, tradedates.unique(),
  dailyProfits,
  uniqueTradingDays.map(
    lets(
      currentDate, current,
      filteredTrades, filter(trades, current.prop("⏰CLOSE TIME").formatDate("DD-MM-YYYY") == currentDate),
      dailyProfit, filteredTrades.map(current.prop("Profit")).sum(),
      dailyProfit
    )
  ),
  dailyProfits
)