Net Buys/Sells Conundrum: Very Small Nets Sometimes

[PeteCresswell]

Got a trading app.

People buy and people sell.

Sales get allocated across funds.

Somebody might buy 1,000 shares of something and put 500 in FundA, 300
in FundB, and 200 in FundC.

Or, they might tell the "Buy" screen to allocate by percentage: 50%
FundA, 30% FundB, and 20% FundC.

Then they might sell 1,000...shares allocated across various "Buy"
transactions.

Problem is that somehow I've created the occasional situation where
all the buys and all the sells for a given fund do not net out to zero
even though the user has sold everything.

We're talking Sum(Shares) being something out around 12-15 decimal
places - as in .000000000394687

First thing that comes to mind is Numeric Double fields and
arithmetic.

But my immediate need is to clean up what the user sees in such a
situation.

I guess I could force the format somehow so that anything less than,
say, .9 shares comes up as zero, but there would still be a row
returned for that fund - as when the buys total 1000 and the sells
total 999.999991.

Am I making sense here?

If so, can somebody comment?

 

[Arvin Meyer [MVP]]

You are correct, it is because of floating point binary math. What I would
do is at every sell, check the remaining shares and if they total less than
1 (there are no fractional shares, I presume), just set the total to zero.
If you don't have a total (a relational database shouldn't store
calculations), use an expression that shows a zero value, if the value is
less than 1.

 

[John W. Vinson]

First thing that comes to mind is Numeric Double fields and
arithmetic.

Perzactly.

That kind of roundoff error is a good reason NOT to use that datatype for this
type of data. *Do* you allow fractional shares? If not, consider using a Long
Integer datatype; if you need up to four decimals use a Currency datatype; in
any case, explicitly round your calculations to the appropriate interval.