Need help to sum values in a table with inline fields for stock tracking

Help
8

The Dataview query isn’t doing what you think it’s doing. Dataview doesn’t have a native way to examine the contents of markdown tables. It wants to search for fields in either the YAML front matter

---
quantity: 1
price: 200
---

or inline fields within you note, like…

quantity:: 1
price:: 200

Your dataview query would make lots of sense if you stored all your transactions in one location or spread out in other notes like in your daily notes. Then you could query for those values and suck them into your specific stock note.

For example, lets say you have one note called “Holdings” that has all your transactions and uses inline fields. Notice there’s an Microsoft transaction as well. You would have all your purchases stored in here.

Holdings Note

- (ticker:: AAPL) (date:: 2024-09-01) (action:: market-buy) [qty:: 1.1111] [price:: 200]
- (ticker:: AAPL) (date:: 2024-11-01) (action:: market-buy) [qty:: 2] [price:: 200]
- (ticker:: MSFT) (date:: 2024-11-01) (action:: market-buy) [qty:: 0.5] [price:: 500]

Then let’s say you have your Apple Note with its own inline field ticker:: AAPL (notice the double colon ::)

Apple Note

## ticker:: AAPL 
name: Apple Inc.  
isin: US0378331005 
sector: Technology  
industry: Consumer Electronics  
market-cap: Mega Cap  
stock-type: equity

## Transactions
```dataviewjs
// initialize stacks with objects whose "place" link to this location.
let result = await dv.query(`
TABLE WITHOUT ID I.ticker as Ticker, I.date as Date, I.action as Action, I.qty as Quantity, I.price as Price, 
round((I.qty*I.price*100)/100,2) as Total
FROM "Holdings"
FLATTEN file.lists AS I
WHERE contains(I.ticker, this.ticker)
`);

if ( result.successful ) {
  const values = result.value.values;
  const numShares = values.map(v => v[3]) // <-- Column to sum
	.reduce((acc, tmp) => acc + tmp);
  const totalCost = values.map(v => v[5]) // <-- Column to sum
    .reduce((acc, tmp) => acc + parseFloat(tmp), 0.0);

  values.push(["","","Shares Owned",numShares,"Total Investment (£)",
    (Math.round(totalCost*100)/100).toFixed(2),
  ])

  dv.table(result.value.headers, values)
} else
  dv.paragraph("~~~~\n" + result.error + "\n~~~~")
```

which would result in the following

Screenshot_20250107_084859
Screenshot_20250107_084859985×729 41 KB

I stole and modified the above Dataview code from @holroy. It behaves as follows…

  • It goes out the the Holdings note, looks for all list items that match the inline ticker field we used in the Apple note header, ticker:: AAPL.
  • Using the values returned from “Holdings”, it creates the Ticker, Date, Action, Quantity, and Price columns. It also calculates the Total column from the Quantity and Price
  • It then calculates the grand totals for the Quantity and Total

Another solution would be to use the Advanced Tables plugin and the formula capability it offers. The issue with this is that the formulas are cryptic at best, and require the reader to manually push a calculate button each time the table is modified. The upside is that your table is future proof since the resulting markdown table is static and no longer requires a plugin to display the results.

I created an example formula to calculate the Total column as well as a grand total…

| Date       | Action     | Quantity | Price  |   Total |
| ---------- | ---------- | -------- | ------ | -------:|
| 2024-09-01 | market-buy | 1        | 200    |  200.00 |
| 2024-11-01 | market-buy | 2        | 200    |  400.00 |
| 2024-11-01 | market-buy | 3        | 100.50 |  301.50 |
| 2024-11-01 | market-buy | 5        | 20.00  |  100.00 |
|            |            | 11       |        | 1001.50 |
<!-- TBLFM: $>=($3 * $4);%.2f -->
<!-- TBLFM: @>$3=sum(@I..@-1) -->
<!-- TBLFM: @>$5=sum(@I..@-1);%.2f -->

A brief explanation of the formulas

<!-- TBLFM: $>=($3 * $4);%.2f -->

Takes column 3 and multiplies it by column 4 and stores the results in the last column with two decimals of precision.

<!-- TBLFM: @>$3=sum(@I..@-1) -->

Sums column 3 and writes it to the last row.

<!-- TBLFM: @>$5=sum(@I..@-1);%.2f -->

Sums column 5 and writes it to the last row.

[Edited: tried to improve poor grammar and added total descriptions]

1 Like