One-to-one matching for capital gains

[Sanjay_S]

I have a series of transactions across various customer folios, in different mutual fund products, which are characterized by the fields [Folio_No] and [Prod_Code]. In the appended files, I have restricted the data to one Folio_No and Prod_Code, for simplicity.

Transactions can either be buys ([T_Type]="P") or sells ([T_Type]="R").

I have arrived at the set of transactions in Table-1 (both excel and Access DBase files attached), after adjusting redemptions against purchases in a FiFO method.

So, the sum total of purchase units in Table-1 equals the sum total of redeemed units.

The next step is to match redemptions to purchases as shown in the attached excel, to calculate the gains. We need to do this in a unit-matching manner as shown rather than just do an overall gains, as the nature of the gains depends on the difference between the redemption and purchase dates, and the tax treatment differs by the nature of the gain.

While I could arrive at Table-1 by just setting off the total redemptions in a folio/product against purchases going from earliest to latest, I am at a loss on how to proceed here.

In this case each redemption or purchase has to be examined, and either split into two or more parts, to ensure unit matching.

Any help in getting the right direction on this would be appreciated.

I am appending Table-1 in an access database also, for easy reference, and experimentation with queries/code.

View attachment Tax_Capital_Gains_Illustration_New.xlsx

View attachment CG_Sample.accdb

Sanjay

 

[The_Doc_Man]

The presence of the possibility that a split or merge could occur for one type of transaction but that isn't matched by the other one will make this a really painful and tedious process.

I think this means that if you are going to mechanically have to split a transaction, you need create appropriate "split" records in the table before any matching can occur AND you will need some other ways to assert the matching.

What is not clear is what drives the split. Do you mean that if the folio has a purchase of 100,000 units but the redemption occurs as transactions of 60,000 units one day and 40,000 units some time later, you have to treat each redemption as though it had been purchased separately in the exact unit size?

To do the matching you describe, I think you must synthesize TWO records of purchase (of 60,000 and 40,000) to replace the 100,000 unit purchase. You would do this during your FIFO sweep. You would have to add some fields to the table to show that you are splitting for the purpose of your special unit-matching treatment, not because the person bought more units. I am not sure what implications this would have on your record-keeping but I'm sure it is already complicated simply because the government is involved for financial management firms anyway.

I'm saying it this way because you claim to need to assure unit-matching. Access won't match things very easily unless you have some type of comparison that would reveal that they should be matched. I can understand that you would need to do this "splitting" in two places - either splitting a purchase OR splitting a redemption. Offhand, I'm not familiar enough with that class of accounting rules to go any deeper. I'll have to defer to any forum members who are intimately familiar with tax rules.

 

[Sanjay_S]

Do you mean that if the folio has a purchase of 100,000 units but the redemption occurs as transactions of 60,000 units one day and 40,000 units some time later, you have to treat each redemption as though it had been purchased separately in the exact unit size?

This is correct, and it is also correct that in some cases we need to split the purchase transactions, and in other cases the redemption transactions.

I can, of course do this manually in excel, but it takes forever. I was hoping that I could get some pointers on implementing this in Access.

I'm not looking for finished code, but if I could be shown the way, I'll do my best to finish this without too many cries for help

Thanks a ton for reading through my problem and sharing your thoughts.

Sanjay

 

[The_Doc_Man]

As to the purely mechanical aspect, here is a thought.

I am fairly sure that SQL cannot do this for you. Therefore, you would need code that opens two recordsets - purchases and redemptions. Considering your FIFO matching comments, I think that means that the recordsets must both be based on queries using ORDER BY of your date fields, oldest records first (i.e. conventional or ASCENDING sort order.)

You will probably need to add fields to your tables to show whether a record has been processed for your FIFO before, and make the recordset queries IGNORE all existing records so marked on either side of the process. That will keep this from being an incredibly ponderous process. The method I'm about to show you creates "virtual" sub-records for matching purposes, so you would need several kinds of markers (could be just a bunch of Boolean or T/F flags) to show whether the record is original purchase/redemption data or a virtual record created by the FIFO sweep.

Open the two recordsets to the first record of each set. Build a Do...Loop program loop that actually doesn't have an exit criterion. (Trust me on this - you'll see what I mean later.)

Now in your code, compare the purchase and redemption amounts for the currently active records. You will get one of four possible results.

1. Amounts are equal - update data in either/both records to show that these two records can be matched directly. In this case, assure that you mark both records as having been processed even though you didn't have to split them. (Prevents processing them again the next time you have to run the FIFO sweep.)

2. Purchase > Redemption - mark the purchase to show that it is being split by the FIFO routine. This might require one .Edit of the Purchases recordset for marking the record being split, plus it WILL require two .AddNew on the Purchases to create two "virtual" records representing the split amounts for FIFO matching. The first record will be of a size to match the Redemption. The second record will be what is left over from the split after you matched the redemption. However you approach this, you will end up with one matched set of records (actual redemption and virtual purchase) plus another purchase record that will become the "current" purchase (virtual) record for the next iteration of the loop.

3. Purchase < Redemption - here, you do the same thing #2 does, but you do the split on the redemption side, not the purchase side.

4. At the bottom of the loop, when you are about to go back to do the compare for the next step of the loop, test whether EITHER recordset is at now at .EOF=True condition. The trick here is that if a split occurred, you DON'T advance the recordset pointer for that side, because your last act of #2 or #3 was to create a new (virtual) record that you want to use for the next comparison. If BOTH sides matched (i.e. the #1 case) you advance (.MoveNext) BOTH recordsets. If a split occurred, you only advance the recordset that WASN'T split - but instead was matched to a split.

If at this point you advance either recordset and hit .EOF, exit the loop using an Exit Do statement. This "fuzzy" exit condition is why the original syntax of the loop was not hard-coded on .EOF conditions. Note that you COULD write the loop to be based on the OR of purchases.EOF OR redemptions.EOF - but you might see the loop end prematurely in cases where you have a partial redemption/purchase match after a split.

When you report the account's actual history, you only report the non-virtual records because you never actually delete those - you just mark them as having been virtualized. And if it happens that two original records DID match, you can report the records. You never report on virtual records but your matching process can use either virtual or non-virtual records.

Wrinkles that might get involved here: You might choose that in the case that you are matching two equal records, at least one of which was an original purchase or redemption, that you copy the original record as a virtual record so that you only would match virtual records. That is almost a matter of personal preference.

You might also wish to have a second pair of tables used ONLY for the matching process, in which case your original table is never updated by the FIFO but a copy of that table IS updated. You do the splits on a FIFO table but do the reports on the original table for history purposes. You do the reports on the FIFO table to show the matching records used for your special treatments.

Whichever way you do this, your loop ends when either side runs out of records.

This is NOT an easy comparison. Read it carefully and if you don't understand it, it is fair to ask questions. However, I do not write this kind of code often and it could be massive. Nor am I familiar with any regulations that might apply since this is personal financial data and I don't even know for which nation. Nor do I have the time to actually try to write and debug something like this. However, it is similar to an algorithm I once used for a former employer for analysis of network connection information.

 

[CJ_London]

as an accountant, the requirement sound very similar to the need to match sales receipts to sales invoices (or purchase payments to supplier invoices).

The way I tackle that is to have a matching table that links to the transactions table on a many to many basis. Something like

tblTransactions
TranPK..AcctFK...TranType…TranValue...Date
3...……..1...……….Inv...……….100...…….1/1
8...……..1...……...Inv...……….150...…….2/1
9...….....1...……...Rect...……..-200..…….3/1
15...…...1...……...Rect...……..-50...……..4/1

tblMatches
MatchPK...Date...Description
4...………….3/1.....some descriptive text here or perhaps other fields
10.………...4/1...…

tblMatchTrans
MatchPK..MatchFK..TranFK...MatchValue
1...………...4...........3...………-100
2...…………4…………...8...….….-100
3...………...4...........9...……..200
4...………...10...……..8.....……-50
5...………….10...…....9...……….50

The sum of MatchValue per matchFK always =0

to see the balance per any transaction, the view is transaction value-sum of match value joining on tranFK.

In finance users usually can select which invoices a payment can be allocated to (or to allocate a part payment). In your case you need quantities and unit prices rather than value- so these could go in the matchtrans table for onward calculation

 

[Gasman]

Cross posted at https://www.utteraccess.com/forum/index.php?showtopic=2051731&pid=2704027&st=0&#entry2704027

 

[The_Doc_Man]

Sanjay - Please note that it is a matter of etiquette that if you post the same question on more than one forum, that you reveal the fact of the cross-post. Failure to do so can result in one of us spending time on a problem that has already been solved elsewhere. We understand the desire to find answers. Not saying you can't get answers here even after having cross-posted. But it is considered bad manners to cross-post without warning us.

 

[Sanjay_S]

Sanjay - Please note that it is a matter of etiquette that if you post the same question on more than one forum, that you reveal the fact of the cross-post. Failure to do so can result in one of us spending time on a problem that has already been solved elsewhere. We understand the desire to find answers. Not saying you can't get answers here even after having cross-posted. But it is considered bad manners to cross-post without warning us.

My apologies to all for the unintended breach. Thank you for the detailed break-down. I will start work on this and I hope I can come back with questions if I hit barriers.

Sanjay

 

[Sanjay_S]

As to the purely mechanical aspect, here is a thought.

I am fairly sure that SQL cannot do this for you. Therefore, you would need code that opens two recordsets - purchases and redemptions. Considering your FIFO matching comments, I think that means that the recordsets must both be based on queries using ORDER BY of your date fields, oldest records first (i.e. conventional or ASCENDING sort order.)

You will probably need to add fields to your tables to show whether a record has been processed for your FIFO before, and make the recordset queries IGNORE all existing records so marked on either side of the process. That will keep this from being an incredibly ponderous process. The method I'm about to show you creates "virtual" sub-records for matching purposes, so you would need several kinds of markers (could be just a bunch of Boolean or T/F flags) to show whether the record is original purchase/redemption data or a virtual record created by the FIFO sweep.

Open the two recordsets to the first record of each set. Build a Do...Loop program loop that actually doesn't have an exit criterion. (Trust me on this - you'll see what I mean later.)

Now in your code, compare the purchase and redemption amounts for the currently active records. You will get one of four possible results.

1. Amounts are equal - update data in either/both records to show that these two records can be matched directly. In this case, assure that you mark both records as having been processed even though you didn't have to split them. (Prevents processing them again the next time you have to run the FIFO sweep.)

2. Purchase > Redemption - mark the purchase to show that it is being split by the FIFO routine. This might require one .Edit of the Purchases recordset for marking the record being split, plus it WILL require two .AddNew on the Purchases to create two "virtual" records representing the split amounts for FIFO matching. The first record will be of a size to match the Redemption. The second record will be what is left over from the split after you matched the redemption. However you approach this, you will end up with one matched set of records (actual redemption and virtual purchase) plus another purchase record that will become the "current" purchase (virtual) record for the next iteration of the loop.

3. Purchase < Redemption - here, you do the same thing #2 does, but you do the split on the redemption side, not the purchase side.

4. At the bottom of the loop, when you are about to go back to do the compare for the next step of the loop, test whether EITHER recordset is at now at .EOF=True condition. The trick here is that if a split occurred, you DON'T advance the recordset pointer for that side, because your last act of #2 or #3 was to create a new (virtual) record that you want to use for the next comparison. If BOTH sides matched (i.e. the #1 case) you advance (.MoveNext) BOTH recordsets. If a split occurred, you only advance the recordset that WASN'T split - but instead was matched to a split.

If at this point you advance either recordset and hit .EOF, exit the loop using an Exit Do statement. This "fuzzy" exit condition is why the original syntax of the loop was not hard-coded on .EOF conditions. Note that you COULD write the loop to be based on the OR of purchases.EOF OR redemptions.EOF - but you might see the loop end prematurely in cases where you have a partial redemption/purchase match after a split.

When you report the account's actual history, you only report the non-virtual records because you never actually delete those - you just mark them as having been virtualized. And if it happens that two original records DID match, you can report the records. You never report on virtual records but your matching process can use either virtual or non-virtual records.

Wrinkles that might get involved here: You might choose that in the case that you are matching two equal records, at least one of which was an original purchase or redemption, that you copy the original record as a virtual record so that you only would match virtual records. That is almost a matter of personal preference.

You might also wish to have a second pair of tables used ONLY for the matching process, in which case your original table is never updated by the FIFO but a copy of that table IS updated. You do the splits on a FIFO table but do the reports on the original table for history purposes. You do the reports on the FIFO table to show the matching records used for your special treatments.

Whichever way you do this, your loop ends when either side runs out of records.

This is NOT an easy comparison. Read it carefully and if you don't understand it, it is fair to ask questions. However, I do not write this kind of code often and it could be massive. Nor am I familiar with any regulations that might apply since this is personal financial data and I don't even know for which nation. Nor do I have the time to actually try to write and debug something like this. However, it is similar to an algorithm I once used for a former employer for analysis of network connection information.

I just read through your method again, after doing some reading on recordsets. Thank you very much for this. I think I should be able to progress on this, and will keep you updated on the progress.

 

[The_Doc_Man]

Good luck. It's not a trivial process but something along these lines SHOULD work. I used something LIKE this with the U.S. Navy once but it wasn't financial in nature when I used it.

 

[Sanjay_S]

As to the purely mechanical aspect, here is a thought.

I am fairly sure that SQL cannot do this for you. Therefore, you would need code that opens two recordsets - purchases and redemptions. Considering your FIFO matching comments, I think that means that the recordsets must both be based on queries using ORDER BY of your date fields, oldest records first (i.e. conventional or ASCENDING sort order.)

You will probably need to add fields to your tables to show whether a record has been processed for your FIFO before, and make the recordset queries IGNORE all existing records so marked on either side of the process. That will keep this from being an incredibly ponderous process. The method I'm about to show you creates "virtual" sub-records for matching purposes, so you would need several kinds of markers (could be just a bunch of Boolean or T/F flags) to show whether the record is original purchase/redemption data or a virtual record created by the FIFO sweep.

Open the two recordsets to the first record of each set. Build a Do...Loop program loop that actually doesn't have an exit criterion. (Trust me on this - you'll see what I mean later.)

Now in your code, compare the purchase and redemption amounts for the currently active records. You will get one of four possible results.

1. Amounts are equal - update data in either/both records to show that these two records can be matched directly. In this case, assure that you mark both records as having been processed even though you didn't have to split them. (Prevents processing them again the next time you have to run the FIFO sweep.)

2. Purchase > Redemption - mark the purchase to show that it is being split by the FIFO routine. This might require one .Edit of the Purchases recordset for marking the record being split, plus it WILL require two .AddNew on the Purchases to create two "virtual" records representing the split amounts for FIFO matching. The first record will be of a size to match the Redemption. The second record will be what is left over from the split after you matched the redemption. However you approach this, you will end up with one matched set of records (actual redemption and virtual purchase) plus another purchase record that will become the "current" purchase (virtual) record for the next iteration of the loop.

3. Purchase < Redemption - here, you do the same thing #2 does, but you do the split on the redemption side, not the purchase side.

4. At the bottom of the loop, when you are about to go back to do the compare for the next step of the loop, test whether EITHER recordset is at now at .EOF=True condition. The trick here is that if a split occurred, you DON'T advance the recordset pointer for that side, because your last act of #2 or #3 was to create a new (virtual) record that you want to use for the next comparison. If BOTH sides matched (i.e. the #1 case) you advance (.MoveNext) BOTH recordsets. If a split occurred, you only advance the recordset that WASN'T split - but instead was matched to a split.

If at this point you advance either recordset and hit .EOF, exit the loop using an Exit Do statement. This "fuzzy" exit condition is why the original syntax of the loop was not hard-coded on .EOF conditions. Note that you COULD write the loop to be based on the OR of purchases.EOF OR redemptions.EOF - but you might see the loop end prematurely in cases where you have a partial redemption/purchase match after a split.

When you report the account's actual history, you only report the non-virtual records because you never actually delete those - you just mark them as having been virtualized. And if it happens that two original records DID match, you can report the records. You never report on virtual records but your matching process can use either virtual or non-virtual records.

Wrinkles that might get involved here: You might choose that in the case that you are matching two equal records, at least one of which was an original purchase or redemption, that you copy the original record as a virtual record so that you only would match virtual records. That is almost a matter of personal preference.

You might also wish to have a second pair of tables used ONLY for the matching process, in which case your original table is never updated by the FIFO but a copy of that table IS updated. You do the splits on a FIFO table but do the reports on the original table for history purposes. You do the reports on the FIFO table to show the matching records used for your special treatments.

Whichever way you do this, your loop ends when either side runs out of records.

This is NOT an easy comparison. Read it carefully and if you don't understand it, it is fair to ask questions. However, I do not write this kind of code often and it could be massive. Nor am I familiar with any regulations that might apply since this is personal financial data and I don't even know for which nation. Nor do I have the time to actually try to write and debug something like this. However, it is similar to an algorithm I once used for a former employer for analysis of network connection information.

The_Doc_Man,

Thank you for a very insightful, detailed breakdown of the approach to solving my problem.

I read up a bit on using recordsets, and managed to write a VBA module that actually did the job.

I am attaching the accdb file here for your perusal. I have put the procedure into a macro, as I have first created a new table for the transactions, and then gone about doing the recordset updates you had mentioned.

I also have a [Match Code] field, a combo of the folio and product code fields. A word of explanation on that:
This data contains only one folio and one product, but the transactions data would contain many folios and products per investor, and I would still need to find a way to make this process cycle through each folio&product combo. But that's tomorrow's battle.

Today, I am just happy that thanks to your advice, I ended up with a module that is doing the job.

Whenever you have a bit of time, please look through the module; I'm sure that my approach can be improved significantly. Would be grateful for any thoughts on improvements.

Sanjay

 

[Sanjay_S]

One bit of advice I received was to reduce the "Else's" in my code, and use "ElseIf" instead.

 

[The_Doc_Man]

Sanjay_S, I am glad that you were able to make some progress with your problem. However, as a personal trait, I hardly ever open up a database provided by someone else. This quirk is personal to me and has nothing to do with you. It is an offshoot of my years of security training with the U.S. Navy as a systems administrator and security administrator.

As to coding style (ELSE vs. ELSEIF, for example), there are a thousand different ways to make anything work. You will develop a style as you go. However, five years from now, you will probably have changed that style at least a couple of times as you discover new methods to do the same thing either faster or more clearly. So don't be surprised that you do things one way today and a different way a few years from now.

 

[Sanjay_S]

Sanjay_S, I am glad that you were able to make some progress with your problem. However, as a personal trait, I hardly ever open up a database provided by someone else. This quirk is personal to me and has nothing to do with you. It is an offshoot of my years of security training with the U.S. Navy as a systems administrator and security administrator.

As to coding style (ELSE vs. ELSEIF, for example), there are a thousand different ways to make anything work. You will develop a style as you go. However, five years from now, you will probably have changed that style at least a couple of times as you discover new methods to do the same thing either faster or more clearly. So don't be surprised that you do things one way today and a different way a few years from now.

Sure, Doc_Man, no problem, and thank you once again for the detailed breakdown. Would have been going around in circles much longer if not fo that.

Sanjay