T
Travis
I'm a financial planner putting together an Access database of my
client's investments. I'm an advanced Word and Excel user but a novice
at Access and a total novice at VBA. I need some advice on how to set
up a table to hold portfolio information.
If the only type of investments I recommended was normal Mutual Funds I
would be able to get away with only the following information:
Client ID
Fund ID (fund ticker)
Number of shares/units owned
Price and date at which they were bought
Because the Fund ID I am using is the industry standard ID which I can
use to look up prices and fund data from the web and other data
sources, these would be the only numbers I would need to store in the
table in order to generate a complete description of the portfolio and
calculate, from published data which I can import into Access or Excel,
the current value of the portfolio, estimated capital gains tax
liabilities etc.
But it gets tricky, because mutual funds aren't the only investments
people might hold.
They may also own shares in companies, in which case I'd need:
Client ID
Stock ID (stock ticker)
Number of shares owned
Price and date at which they were bought.
The database would need to know whether its a share or a mutual fund in
order to know which data table to look up the appropriate price.
I have a table "Investments" which contains a complete list of all the
mutual funds and stocks my clients own, plus quite a few others which
they don't own. This other table has information about the asset
class, the name of the investment manager, basic information about the
fund style, fees etc.
That's not such a big challenge, but I don't know how to deal with
other special assets which aren't publicly quoted assets, for example
"Investment property at 1600 Pennsylvania Avenue". If someone owned
this asset, being unlisted real estate it doesn't have a unit price, so
I'd have to manually enter the price.
So my portfolios table would actually need to look like this:
Client ID
Investment ticker
Investment name if its not an asset with a ticker
Number of units/shares owned
Purchase price
Purchase date
Current unit price, if its not a publicly listed asset.
Maybe there is an easy answer to this, I need to design tables and
forms so if its a stock or mutual fund it can take the latest unit
price from another data source like an Excel spreadsheet or Access
table, if its something else like an investment property it would
prompt me to enter a unit price manually at the time I'm typing in the
portfolio.
I'd also need this manual functioning if I have only incomplete
information, such as being told that someone owns approx $20,000 worth
of Microsoft stock, though not precisely knowing exactly how many
shares that is since I didn't know the price at which the $20,000
figure was quoted. I don't have this kind of ambiguity with
investments that I put in place, but often people come to me with
incomplete information saying they've got $x in their 401K but can't
specify the underlying investments, let alone how many shares they own
of each, because they don't have the documentation at hand.
Any suggestions on the possible layout of these tables?
Thanks in advance.
Travis
client's investments. I'm an advanced Word and Excel user but a novice
at Access and a total novice at VBA. I need some advice on how to set
up a table to hold portfolio information.
If the only type of investments I recommended was normal Mutual Funds I
would be able to get away with only the following information:
Client ID
Fund ID (fund ticker)
Number of shares/units owned
Price and date at which they were bought
Because the Fund ID I am using is the industry standard ID which I can
use to look up prices and fund data from the web and other data
sources, these would be the only numbers I would need to store in the
table in order to generate a complete description of the portfolio and
calculate, from published data which I can import into Access or Excel,
the current value of the portfolio, estimated capital gains tax
liabilities etc.
But it gets tricky, because mutual funds aren't the only investments
people might hold.
They may also own shares in companies, in which case I'd need:
Client ID
Stock ID (stock ticker)
Number of shares owned
Price and date at which they were bought.
The database would need to know whether its a share or a mutual fund in
order to know which data table to look up the appropriate price.
I have a table "Investments" which contains a complete list of all the
mutual funds and stocks my clients own, plus quite a few others which
they don't own. This other table has information about the asset
class, the name of the investment manager, basic information about the
fund style, fees etc.
That's not such a big challenge, but I don't know how to deal with
other special assets which aren't publicly quoted assets, for example
"Investment property at 1600 Pennsylvania Avenue". If someone owned
this asset, being unlisted real estate it doesn't have a unit price, so
I'd have to manually enter the price.
So my portfolios table would actually need to look like this:
Client ID
Investment ticker
Investment name if its not an asset with a ticker
Number of units/shares owned
Purchase price
Purchase date
Current unit price, if its not a publicly listed asset.
Maybe there is an easy answer to this, I need to design tables and
forms so if its a stock or mutual fund it can take the latest unit
price from another data source like an Excel spreadsheet or Access
table, if its something else like an investment property it would
prompt me to enter a unit price manually at the time I'm typing in the
portfolio.
I'd also need this manual functioning if I have only incomplete
information, such as being told that someone owns approx $20,000 worth
of Microsoft stock, though not precisely knowing exactly how many
shares that is since I didn't know the price at which the $20,000
figure was quoted. I don't have this kind of ambiguity with
investments that I put in place, but often people come to me with
incomplete information saying they've got $x in their 401K but can't
specify the underlying investments, let alone how many shares they own
of each, because they don't have the documentation at hand.
Any suggestions on the possible layout of these tables?
Thanks in advance.
Travis