Create sell transaction and buy transaction tables and link both

[The Gardener]

Hello,

Hopefully I have chosen the right forum for this question.

As a part of a database that I am developing which deals with portfolios of mutual funds I need to record a buy transaction of a mutual and a sell transaction of a mutual fund.

I have created a table called new money into investment which records any new [outside] money invested into a mutual fund in a particular portfolio.

I also have a table which records sell transactions where mutual fund units are sold and the resulting cash is taken out of the portfolio [say, into my bank account] that holds the mutual fund units sold.

Now, here I am unsure of what to do:

There is the situation where the units [all or some] of a mutual fund in a particular portfolio are sold for 'x' dollars AND on the same date those same 'x' dollars are used to purchase units of a different mutual fund which will be held inside the same portfolio as the mutual fund which was sold.

I think that I should have a table for sell transactions and a separate table for buy transactions.

My thinking here is that 'buy' and 'sell' are more than just attributes.

And also, separating them into two tables would make it far easier to use or access this data elsewhere if needed.

Also, importantly, in order to to be able to see where the money went from a sell transaction,

I need to somehow be able to link/tie such a sell transaction to the buy transaction

so that it is clear that the money received from the sell transaction was used for the buy transaction.

I am not sure of the best way to do this.

I thought of the date as a linking piece of information, but what if the brokerage has to delay the 'buy' transaction so it is not on the same day.

Or, a script that, when the sell transaction record is committed, brings up a message forcing you to also make a record of the corresponding buy transaction.

But I am still left with making some kind of linking data. A manually entered number [the same number] in each [the sell and the buy records?

Any advice will be greatly appreciated as to the best way to structure this information and link the information.

[Fitch]

It sounds like you're about to dig yourself into a hole. If a transaction includes a buyer and a seller, then those would be attributes of a single transaction, e.g. buyer ID and seller ID. You'd presumably have a table of "traders" or something, whose unique ID would link to either buyer ID or seller ID on the transaction.

It doesn't necessarily make things any easier to split buys and sells into separate tables, in fact the opposite may be true if you want to simply find all the transactions for a given date range and total them, not to mention export them easily. If you need to isolate only the buys or the sells, then a simple Find, relationship, or filtered portal should handle it.

Do you really need to track where each bit of money is spent? I think it's much, much easier to set up an account system that's more like a ledger or checkbook: money comes in, money goes out. Otherwise you are dealing with splits going in both directions and it's especially crazy when you start dealing with refunds and credit (which may not be an issue for you but still -- been there, done that).

[The Gardener]

Hello,

Thank you for replying. I will attempt to clarify some aspects of my initial post. This is a database which is dealing with an investment portfolio that holds mutual funds [it could hold bonds, etfs, stocks for that matter]. It is a single user database. For a transaction, the owner, lets say me, is the seller and the buyer, I am the trader. if I sell a fund for $x then that becomes cash in the portfolio. If I buy a fund I use cash held in the portfolio.

I do not need to find the total of transactions for a date range, nor deal with refunds or credits.

To try to be brief, there are other tables in the database that track the mutual fund holdings recording their book value [purchase cost], market value at a certain date, units held, income received by that particular fund, market price unit value, etc.

The value of being able to have a 'hard' record [not something for display purposes] of a buy or sell is that then you can use it for further calculations. I will try to explain. I buy a fund for $100.00, that becomes the funds book value, it increases to $200.00 in the market, the funds market value, return on investment equals 100%. I sell the fund for $200.00 and buy another fund for $200.00, the new bought funds book value. Based on this info one can calculate [records] various things such as return on invested capital [money] [at a particular date] which is the market value above or below the invested amount, yield [ie. a fund with a cost of $100.00 pays to you $10.00 every year end giving you a yield of 10%. So yes, I need to track where all the money went because then you can see if you made a good or not so good investment decision.

Hopefully this will give you some insight for some further advice.

I have thought about the idea of splits, which I cannot grasp how to set up. ie. I sell a fund for $100.00, buy another fund for $50.00 and have $50.00 into cash in a [same] portfolio.

Thanks in advance for a reply

[comment]

Based on this info one can calculate [records] various things such as return on invested capital [money] [at a particular date] which is the market value above or below the invested amount, yield [ie. a fund with a cost of $100.00 pays to you $10.00 every year end giving you a yield of 10%. So yes, I need to track where all the money went because then you can see if you made a good or not so good investment decision.

Every transaction - buy or sell - has an amount and a date. If you know that you bought Fund A for $100, sold it for $200, bought Fund B for $200 and Fund B is now worth $180, you can calculate the total of the transaction amounts:

-$100 (expended to buy Fund A)

+$200 (received from selling Fund A)

-$200 (expended to buy Fund

+$180 (expected from selling Fund B at current price)

The total of the above produces a balance of $80 earned (of course, the calculation would be quite different if it also considered the transaction dates).

I sell a fund for $100.00, buy another fund for $50.00 and have $50.00 into cash in a [same] portfolio.

I am having a hard time grasping the concept of cash sitting in a portfolio. But if you wanted, I suppose you could record cash flows in and out of the portfolio same as any other transaction, for example:

+$80 (initial cash injection)

-$80 (expended to buy Fund A)

+$100 (received from selling Fund A)

-$50 (expended to buy Fund

The total of the above produces a balance of $50, representing the $50 cash reserve in the portfolio.