Solved Help setting up a new Investment Portfolio database using normalization

[HalloweenWeed]

tblTransTypes
tt_ID, atuonumber, primary key

One more thing: Why have these 3 fields: tt_ID, autonumber, primary key; or is this just one in the same field, fully described? Please clarify. Thank you.

 

[plog]

That's one field. The name, the data type and the description of what it's for

 

[HalloweenWeed]

OK, I think I've done that, but not sure if I should carry this further to combine the tax & fee tables and the cash amount in the transaction table, into a single currency table, and an 'entry type' table to describe whether or not the entry is Tax, Fee, sale, or purchase. Here's what I have so far:

 

[HalloweenWeed]

The problem with combining the cash entries into one table, as far as I know, is that it may necessitate multiple entries for one transaction. One of my original specifications was that I wanted to eliminate multiple entries per transaction. As I believe I stated before, one transaction may or may not have taxes and fees.

 

[HalloweenWeed]

Whoops, I just seen a mistake in table reference links to the Tax table and the Fees table. I will fix...

 

[HalloweenWeed]

Table references fixed, and added the "Entity table." Moved the 'tax entity' field to the account directory:

 

[HalloweenWeed]

Tax is an optional column. It is filled or not filled depending on whether the transaction was taxable. So, it appears for EVERY transaction. If there are rules, you will need to enforce them using code although a flag in your transaction type table might help. I don't know what the rules are so I can't tell you what to do but there are three possibilities for each transaction type. Tax is required; which means it must be filled in. Tax is optional or Tax is N/A and so not allowed. If you can't make a "required" or "never" statement, Tax is most likely optional and might appear on most transaction types.

HI Pat,
This is not like retail sales where every purchase has a domestic tax except for exempt mdse. That said, the tax in question is levied by the home government of the account institution (generally either a Corporation or a fund institution which may or may not be non-profit). So far, I have only seen tax on the sales and dividends of ETF's (Electronically Traded Funds, a term that generally denotes the stock is traded on a foreign stock exchange, and dealt by a 3rd party broker, it is that broker that must levy the taxes according to either that Country's laws, or the laws of the foreign exchange entity such as Hong Kong for instance). That's not to say I won't ever have a non-ETF taxed, I don't know that answer, and I also think one would need to know the future to decide such a thing.

Otherwise, so far, taxes are levied on my personal income, based on the U.S. Income tax, not the domestic trades, and that is accounted for in my personal finances, not this database.

I don't know the rules of these taxes levied on the trades/dividends by foreign entities, and it's also possible that they could change over time. So I don't intend to even learn such rules, there is no need for me to do that, simply to enter the transactions as levied. Remember, this is not accounting for a retail POS, but otherwise this is just like accounting/bookkeeping, which can generate stats. Therefore, tax is optional on a entry-to-entry basis in this database.


So far, at a glance, it looks as if only 10-20% of my transactions are taxed. That's why I moved taxes to another table. For some reason, I thought (or misunderstood?) that when a field is not used in most transactions, part of normalization was to move it to a different table (because it doesn't need to be entered on every transaction). You say it was a column. By that reasoning Fees is also a column, so I need to move fees back to the transaction table too?

 

[HalloweenWeed]

The key is how your portfolio manager reports them to you. Are they separate transactions or fields on a single transaction? You can take their lead.

They are separate transactions by the report from the broker. But I don't want to keep them that way, and I want to enter them simultaneously (on 1 master form).

If "fees" is the sum of all fees,

It is not, per se.

If the fees and taxes need to be linked to the transaction that spawned them, then you would be justified in making a child table to hold transactions on transactions.

There is a need, in showing statistics, to link the fees to the account (for a total or period total), not the transaction itself - except to get the date.
I need a list of taxes paid to each entity after the end-of-year. So taxes tied to entity? Should I just include a "tt_TaxEntity" field in my tax table? Or could that be easily found through the 2 links between the tax table, transaction table, and Account Directory? Seems like doing so would compromise normalization.


ALSO: I just re-added a date field to the transaction table: ta_XactnDate.

 

[HalloweenWeed]

So, if I keep the tables separate, will I need to use subforms on my master form for the tax & fees? Or a single query that accomplishes the same end result? I don't want to make a new record on each one of these every time a new transaction is entered, just whenever the transaction has a tax or fee. I don't know how to do that without relying entirely on vba to record the tax table and the fees table. Wouldn't this mean that if I don't use vba, I need to move the tax and the fees back to the transaction table? I believe that when a new record is created on the master form, it would also create new records on the linked subforms, or the tables linked to by a (single) query. Am I correct?

 

[LarryE]

You should use tiny subforms. All you need to do is to enter an amount, which you have to enter no matter what.


No, that is not correct.

Bound forms handle the problem just fine. If you don't dirty the subform, no record would be created. If you dirty it by entering a tax or fee amt, Access automagically populates the foreign key provided you have the master/child links set correctly.

In the vast majority of forms, the only VBA code you ever need is code in the form's BeforeUpdate event to validate data. Access takes care of pretty much everything else all by itself. You don't need navigation code, although some people like to use custom buttons because that is build in. You don't need save code because that is built in. The code you actually need is the code to prevent saving invalid data. For example, have you ever seen a date like 2/12/203? It happens more frequently than you might imagine. The date is perfectly valid as far as the date data type is concerned. It is just illogical unless you are working with ancient event data but it is a common type of typo that your validation code should catch because the date is out of what would be the logical range for any date in most applications.

May I just butt in here and ask a question? I have been following this because I have built an ACCESS app for this purpose in the past.

If any single transaction can have MORE THAN ONE taxing authority tax amount, then you probably need a separate tax table linked to the transaction table with a TaxID Primary Key and TransactionID as a Foreign Key. So if any transaction can have say U.S. Income Tax affects AND a Foreign tax affect AND/OR maybe a state tax affect, then a tax table is warranted. If not, then a taxing authority name field and amount field (and maybe percentage) could be kept in the transaction table.

 

[HalloweenWeed]

May I just butt in here and ask a question? I have been following this because I have built an ACCESS app for this purpose in the past.

If any single transaction can have MORE THAN ONE taxing authority tax amount, then you probably need a separate tax table linked to the transaction table with a TaxID Primary Key and TransactionID as a Foreign Key. So if any transaction can have say U.S. Income Tax affects AND a Foreign tax affect AND/OR maybe a state tax affect, then a tax table is warranted. If not, then a taxing authority name field and amount field (and maybe percentage) could be kept in the transaction table.

Thank you @LarryE . The answer is no, only one.

 

[HalloweenWeed]

OK, thank you @Pat Hartman . That said, I feel more comfortable with the fields in the transaction table, I feel more confident that I will be able to do what I want with less complications/complexity. Like you said, not making millions of records here (probably just a few hundred in my lifetime).

Here's what I have now, I have eliminated the tax, fees, and split table and inserted those fields into the transaction table.
I have added a 'default settings' table, as that table sets the presently used Broker. To change Brokers, one needs to close all forms/reports and set dflt_BrokerID (presently can be done manually with the 'add/edit Broker Form).
I have made some early-build forms and made some records, but no transactions yet, I haven't even started on the transaction form yet.
I also swapped the direction of the tables in the relationship graphic, makes more sense to me.

 

[HalloweenWeed]

I think I mentioned the possibility a US tax/fee (not clear which) in the future Larry if the White House gets its way.

Yeah, there is a "Domestic" (default) entry in the tax entity table. TY
Also, I think it would probably be on my Income tax return, not in these transactions.

 

[HalloweenWeed]

@HalloweenWeed I guess I never mentioned how annoying prefixes on column names in tables are once you start making queries and writing code. WHY - in the case of queries, all you will see are the prefixes. You will have to constantly expand the column widths to get to any meaningful part of the name. As for code, Intellisense won't kick in until you get past the prefix so you are dramatically reducing its usefulness.

Prefixes are not wrong per se. They are just too annoying for words. If you insist on the concept, suffixes will be much less in your way and do the same thing.

Oh thank God (not literally). I was just doing it because plog showed it. I will remove them.

 

[LarryE]

OK. I use a very simple table structure like this:

1. Each Financial Organization can have multiple accounts (IRA, Investment, Checking Account etc.)
2. Each Account may contain multiple Products ( Cash, Bond, Stock, Mutual Fund etc.)
3. Each Product may have multiple transactions

I also keep a running balance in the transactions table of Product Balances. Now normally, you would not keep calculated values in a table. However, in the case of investment portfolios, people do want to keep a balance history and the dates of those balances. At least I do.

 

[LarryE]

I think I mentioned the possibility a US tax/fee (not clear which) in the future Larry if the White House gets its way.

Don't even get me started.

 

[HalloweenWeed]

OK. I use a very simple table structure like this:
View attachment 109261

1. Each Financial Organization can have multiple accounts (IRA, Investment, Checking Account etc.)
2. Each Account may contain multiple Products ( Cash, Bond, Stock, Mutual Fund etc.)
3. Each Product may have multiple transactions

I also keep a running balance in the transactions table of Product Balances. Now normally, you would not keep calculated values in a table. However, in the case of investment portfolios, people do want to keep a balance history and the dates of those balances. At least I do.

Thank you for that @LarryE
That's a bit more complicated than I want to get. I keep my retirement portfolio separate. This is just for my stock market forays. I may at some time put some money from this coffer into a mutual fund, and that's why I need it in the account types. But as far as I can imagine, I'm not going to want to keep separate track of that other stuff in this database. It is just a tool to help my upcoming investments, and for tax return forms for these foreign taxes. Please, if you beg to differ I would be interested in what you have to say about that. Thank you.

 

[HalloweenWeed]

Hello guys,
I've done some forms and accounts, working on building up to my first entry, haven't finished my transaction form yet, and considering using a separate form for my cash deposits/withdrawals. I've added a 'Cash account' field to my transactions table, due to the need, and when I went to create that link to the account directory I experienced a slight hitch in the relationship linking (after a similar hitch in the relationship between the new 'default cash account' setting in the Broker table). So I'm going to run this by you guys, is this right?:

Remember, the "cash account" a.k.a. "core class" account, changes over time. You cannot recreate the proper accounting if there isn't a record of what it's setting was at the time of the transaction. For each security transaction that involves cash, the cash or security is subtracted from one account and cash or security is added in another. And I don't want to do double-entries of each transaction.

The "default cash account" is unique to each Broker. If the broker changes, so does the "default cash account." So far, the only way to change the Broker, is to change the "default broker" setting in tblDfltSetngs via the Broker form "frmBrokerAddEdit." Should I see the need, I could add the functionality in my upcoming "main menu" form.

Please let me know what you think. Meanwhile, I'm going to work on my (securities?) transaction form. Thank you.

EDITED: misperception - "cash or security" (Cash or shares).

 

[HalloweenWeed]

I'm trying to decide whether to include my "cash transactions" (deposits/withdrawals) in my (securities/main) transaction form. Cash transactions are the only transactions that don't both add an amount in one account, and subtract from another. It might be tricky to adjust the (securities/main) transactions form to do both, and it would need to also inhibit use of changing shares amount. What do you think? If I don't see a response I will decide myself. Thank you.

 

[HalloweenWeed]

Pardon me if I'm asking a question that is too basic or popular, but I am having trouble Googling it as there are too many non-relevant hits:
Does the Form 'before update' event fire before a record is added, e.g. can you use that event for some VBA validation of combinations of field settings on said form, and 'cancel' the addition of a record if the combination is not valid? Or is the record added before all the fields are set?

Or, as an alternative, should I use a VBA "workspace" so I can have the addition nullified? How would I go about that?