If fractional share selected for very first lot, divide-by-zero error occurs when running lot optimization (VMW)

SteveDeJarnett commented 8 months ago

I think I found an issue with the Custom Functions -> Optimize per-lot (VMW)

If the first ESPP lot is used for the fractional share, then the formula in column AI ends up dividing by 0 because there are no AVGO shares in the active quantity column AB. This then ripples throughout the sheets because the IFERROR logic in column AI fils in an "n/a" value that can't be coerced into a number.
I can work around this, somewhat incorrectly, by filling in a value of "1" in column AA or AB, but this is a hack and will result in incorrect values.
I can also just remove the "fractional share" checkbox which is probably less-incorrect than the hack above, but requires separate work to calculate cost basis for that lot and for taxes on the fractional share.

Per review by @hickeng comments in Gchat: For this issue specifically - I need to add logic that prevents a lot being selected for cash if there's a fractional share associated with it as that's not a valid choice. You cannot have the lot be all cash because the fraction must have come from it. Conversion happens THEN then fraction is sold - they're sequential.

hickeng commented 8 months ago

@SteveDeJarnett I've reproduced this... and I'm tempted to leave it as is as it seems highly like there's an input error and getting a sheet error as a result is not a bad way of flagging that. If you've not got any shares in a lot, then it cannot be used for sale of the fractional share.

Was this you just trying out the checkbox, or did you somehow end up needing to use a lot with 0 shares for the fraction?

SteveDeJarnett commented 8 months ago

This was on an actual lot with shares. I don't believe there was an input error but I am happy to check again on the next version. I believe I encountered this on 1.2 or 1.3 version.

hickeng commented 8 months ago

This was on an actual lot with shares. I don't believe there was an input error but I am happy to check again on the next version. I believe I encountered this on 1.2 or 1.3 version.

Please do. If I adds shares to the first ESPP lot in the sheet and check the box, everything looks sane.

SteveDeJarnett commented 8 months ago

I confirm this is working fine in version 1.6 and 1.6b of the sheet. Closing.

SteveDeJarnett commented 8 months ago

Working as expected in versions 1.6 and 1.6b

hickeng / financial

If fractional share selected for very first lot, divide-by-zero error occurs when running lot optimization (VMW) #79