If you didn’t see it yet, I created using MS Dynamics NAV an application for bookkeeping stock exchange transactions.
The description and Youtube presentation playlist can be found in this Sample Project: https://andreilungu.com/stock-exchange-transactions-bookkeeping-app/
In the 2nd part of the presentation I also showed that I used SumIndexFields in order to calculate Sums in an efficient way. In this post I want to add more details about this.
So I have a table with stock exchange transactions per Symbol and I want to calculate the Average Buy Price per each Symbol (after each Buy transaction):
The Average Buy Price for one symbol is the Sum of Amount of the entries per that symbol divided by the Sum of Quantity.
Example: We buy 100 Shares at 10 euros/share and 100 shares at 20. Average Buy Price is (100 * 10 + 100 * 20) / (100+100) = 15 euros
It is not good from a performance standpoint to loop through each record and increment the sum if the conditions are met. In Dynamics NAV there are SumIndexFields that can be used for cases like this. This technology lets you calculate the sums of numeric data type (Decimal, Integer, BigInteger, and Duration) columns in tables, even in tables with thousands of records very fast.
MSDN LINK: https://msdn.microsoft.com/en-us/library/dd355336(v=nav.90).aspx
How is it working ?
Let’s see on my example:
For table BVB Entry I created a secondary key for Symbol and Account fields and added 2 SumIndexFields: Quantity and Amount.
By doing this, NAV created in SQL Server an Indexed View which is maintained every time I am adding new transactions to the table. The view contains Symbol and Account fields (the fields from the secondary key), a field with the count of transactions that make the total and the 2 SumIndexFields (Sum of Quantity and Sum of Amount per Symbol and Account)
For example, before the last 4 entries were added, for Symbol Code “SNG” I had Sum of Quantity 500 and Sum of Amount 13272. The average buy Price is 13272 / 500 = 26.5445
You can see these amounts in SQL Server here, already calculated:
I created in the application also a page where I display the Average Buy Price for each Symbol. You can see it bellow:
After I added also the last 4 transactions (SELL of 100 Shares at 33.4 without commission) the Quantity for SNG became 500-400 = 100 and the Amount 13272 – 10618 = 2654.
If I select again the data from the View in SQL Server I can see that the data was updated:
I can write code to use the values from this view when I need to calculate my totals for the average price in this way:
BVBEntry.SETCURRENTKEY(Symbol,Account); BVBEntry.SETRANGE(Symbol, pSymbol); BVBEntry.SETRANGE(Account,AccountTable.Code); IF BVBEntry.CALCSUMS(Quantity) THEN SumQ := BVBEntry.Quantity; IF BVBEntry.CALCSUMS(Amount) THEN SumTotalAmount := BVBEntry.Amount; IF SumQ > 0 THEN AvgPrice := SumTotalAmount / SumQ;
Imagine that instead the table from my example you have a table with 1 million rows. I ensure you that searching within each row to calculate a total will take a lot of time compared to searching in the Indexed View for some already precalculated entries 🙂
Be careful and read also the documentation related to performance of SIFT https://msdn.microsoft.com/en-us/library/dd338941(v=nav.90).aspx. Using SIFT when it is not necessary can lower performance because the Indexed Views must be updated after each new transaction in order to be used for calculations.
That is really nice explanation. Thanking you !