Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cleaner way to implement this design?

Author  Topic 

Nigelsd
Starting Member

14 Posts
Posted - 2006-04-15 : 21:31:41
Hi all, I was hoping I could get some ideas about how to implement a share portfolio. Right now I've got a transaction table that lists the name of the share, quantity, amount and a few other details of every transaction I make. I think I'll be better off breaking it up into two tables; sales and purchases.

I want to create a portfolio using this data. I figured that I could set a query to show one column with names of all the shares (selected from the share purchase table) and another column to work out the current number of shares I hold. Thing is, I can't seem to work out a formula that will pick up all purchases of a certain share, then subtract of sales of the same and then place it in the column for that share in the portfolio.

There's probably a much cleaner way to do it that I'm completely missing. Any ideas? Perhaps edit the input form for the transaction table(s) so that it notes the name of the share and creates a new record if the share isn't already in the portfolio or adjusts it if it is.

And yes, I'm using Access.

Thanks
-N

nr
SQLTeam MVY

12543 Posts
Posted - 2006-04-15 : 21:56:26
A share table
A trade table (sell/purchase is an attribute)
A price table - holding prices of shares for valuation - depends what you need but often the closing price for every day.

From this you can query the position and value of the portfolio (you might want to trade withing different portfolios) but it might be better to maintain a table of the position in each share. For this I would just maintain the position movements - i.e. share, startdate, enddate, position (possible portfolio).

From this you can calculate the value at any date by using the price table.

other things to think about - do you want to record cashflow, didvidends, etc.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Nigelsd
Starting Member

14 Posts
Posted - 2006-04-16 : 06:34:58
Cheers nr,

having thought about it some more, I figured that instead of a "Share Transactions" table, I'll have a "General Transactions" table as such:

General Transaction {Transaction ID#, Date, Transaction code, Details, Unit Price, Quantity, Amount}

Transaction Codes {Transaction Code#, Details (Share purchase, share sale, broker fee, etc)}



However, I realized that the Gen transactions would have lot of nulls. For example, if I made an entry for a broker's fee, it would not have a quantity, unit price, etc.). This won't really have a negative impact on anything, but I'd like the design to be cleaner.

Also, if I wanted to then run a query to see my current portfolio, I'm not quite sure how I'd go about writing it up. The Gen transactions table isn't linked to share names in any way, so I can't isolate the shares I want to look at.
Unless I start creating accounts for every single share I buy/sell. Oooh boy.


Finally, is there an IF THEN statement in SQL? When making share transactions, I want to automatically make a broker's fee transaction such that it is either a certain %age of the transaction or $X, whichever is higher. I'd imagine it would have something to do with the form I create to input a transaction...I'm just not sure what. Ideally, I would think that the form should have radio buttons to ask if it is a share transaction or a non-share transaction. If it's a share transaction then broker calculations need to be made in the background.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2006-04-16 : 09:07:39
is there an IF THEN statement in SQL?
have a look at the case statement.
if...then is used for control of flow, in an sql statement - case when then end.

As for broker fees this is a cash flow. It can be represented in the transaction table if you wish - often via a unit price of 1 and quantity of the value - just so that quantity * price = value.
The share entry for this could be the currency. Doing this you would have a double entry for each share purchase - one showing the share position increasing and the other the currency position decreasing.

In your share table (or instrument/security/TradingEntity/...) you will need to have every share that you buy and this will link to the transaction table. It will also link to your price table. Getting the current portfolio info then becomes simple - sum the trade values (or maintain them in an aggregate table) and use the price table for the current valuation (I would use a position table for this) - you can also use the price table and position to show a graph of the valuation and P&L over time.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2006-04-16 : 09:10:41
By the way are you just dealing with equities not more complicated instruments - I would have thought so from the questions.

What are you going to do about things like share splits, takeovers, etc.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Nigelsd
Starting Member

14 Posts
Posted - 2006-04-16 : 10:50:12
quote:
Originally posted by nr

As for broker fees this is a cash flow. It can be represented in the transaction table if you wish - often via a unit price of 1 and quantity of the value - just so that quantity * price = value.
The share entry for this could be the currency. Doing this you would have a double entry for each share purchase - one showing the share position increasing and the other the currency position decreasing.


Yes, of course! How did I miss that?



quote:
In your share table (or instrument/security/TradingEntity/...) you will need to have every share that you buy and this will link to the transaction table. It will also link to your price table.


I thought of it as an ER diagram and came up with one possible solution.

I'll create an "Is a Share" table such that:
Is a Share {Transaction ID#, Share ID#}

And

Share List {Share ID#, Name, Random details}

Share Price History {Price Hist ID#, Date, Name, Value, Share ID}

Am I on the right track? If I am, then how would I go about constructing a statement to go through the Share List and come up with a portfolio? Something tells me that the "Group By" statement is part of the solution.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2006-04-16 : 11:01:09
Why do you need the "Is a Share" table? I would have thought the transaction table would contain the Share ID.

try
Share (Share_id, name, other attributes)
Transaction(Type, Share_id, Quantity, Price)
PriceHistory (Share_id, Date, Price)
Position(Share_id, StartDate, EndDate, Position)

Now for the total current value per share
select p.Share_id, sum(p.Position * ph.Price)
from Position p
join (select * from PriceHistory where Date = convert(varchar(8), getdate(), 112) ph
on ph.Share_id = p.Share_id
where getdate() between p.StartDate and p.EndDate

or if you don't maintain a position table
select p.Share_id, sum(p.Position * ph.Price)
from (select share_id, Quantity = sum(Quantity * case when TradeType = 'purchase' then 1 else -1 end) from Transaction where sum(Quantity * case when TradeType = 'purchase' then 1 else -1 end) > 0) p
join (select * from PriceHistory where Date = convert(varchar(8), getdate(), 112) ph
on ph.Share_id = p.Share_id
where getdate() between p.StartDate and p.EndDate


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Nigelsd
Starting Member

14 Posts
Posted - 2006-04-16 : 11:09:07
Why do you need the "Is a Share" table? I would have thought the transaction table would contain the Share ID

I thought that if I put a "Share ID" foreign key in the General Transaction table I would have a lot of nulls when entering non-share transactions like broker fees and the like.


Also, thinking about balance sheets, at the moment my transaction table is:

General Transaction {Transaction ID#, Date, Transaction code, Details, Unit Price, Quantity, Debit/Credit}

I can't think of a way to derive a current profit figure. The three steps I can think of are:
1. Seperate debit and credit
2. Add each one (seperately)
3. Subtract credit from debit.

I can think of ways to do each thing one at a time but can't work out how to do it all in one go. Any ideas?
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2006-04-16 : 11:16:17
Have a look at the query I gave.
I assumed that debit/credit would be implied by the transaction type - in fact it's common to have another table with attributes for the transaction type to say how it affects the position.
I would have all entries in the transaction table referring to entries in the share (not a good name now) table as you will probably need to hold other properties.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Nigelsd
Starting Member

14 Posts
Posted - 2006-04-16 : 11:17:03
[quotetry
Share (Share_id, name, other attributes)
Transaction(Type, Share_id, Quantity, Price)
PriceHistory (Share_id, Date, Price)
Position(Share_id, StartDate, EndDate, Position)

Now for the total current value per share
select p.Share_id, sum(p.Position * ph.Price)
from Position p
join (select * from PriceHistory where Date = convert(varchar(8), getdate(), 112) ph
on ph.Share_id = p.Share_id
where getdate() between p.StartDate and p.EndDate
[/quote]

Oooh Aaah. My Access for Dummies guide hasn't quite prepared me for this but I think I understand the logic behind it.
Go to Top of Page

Nigelsd
Starting Member

14 Posts
Posted - 2006-04-16 : 11:26:47
quote:
Originally posted by nr

I assumed that debit/credit would be implied by the transaction type - in fact it's common to have another table with attributes for the transaction type to say how it affects the position.


I'd earlier described my "Transaction Codes" table.

Transaction Codes {Transaction Code#, Details (Share purchase, share sale, broker fee, etc)}

Let's say I add another attribute here called "Debit/Credit". I'd still have to seperate all DR and CR, add them up seperately and subtract one from the other....which would bring me back to square one? I think I know how to adapt your query, but I want to be able to understand it perfectly before doing so.


One thing I thought of is to show all Credit figures as negative instead of having debit/credit columns (somewhat ugly, I know). By doing this, I can setup an action query to Group By Sum. That would list out my transaction and find the sum of the debits and the (negative) credits.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2006-04-16 : 11:46:38
>> I'd still have to seperate all DR and CR, add them up seperately and subtract one from the other
Nope - it's a single query to get that.
It's in the derived table p in my query - I would maintain the position table though so it's already aggregated.

Have a look at my query - it does that using the transaction code.
I would advise against positive and negaitive values - if you end up with a more complex system then entries will have different affects depending on how they are aggregated. That's easy if you use an attribute of the transaction code table but not if you use the sign in the transaction table.

Consider
TransactionType
ID
Description
PositionAffect -1, 0, 1

then
select p.Share_id, sum(p.Position * ph.Price)
from (select t.share_id, t.Quantity = sum(t.Quantity * tt.PositionAffect) from Transaction t join TransactionType tt on tt.TransactionType = t.Type where sum(Quantity * * tt.PositionAffect) p
join (select * from PriceHistory where Date = convert(varchar(8), getdate(), 112) ph
on ph.Share_id = p.Share_id
where getdate() between p.StartDate and p.EndDate

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Nigelsd
Starting Member

14 Posts
Posted - 2006-04-16 : 13:14:44
Right, I haven't come across derived tables before but now that I know what p is the code is becoming clearer.

What I don't understand is

Date = convert(varchar(8), getdate(), 112) ph
on ph.Share_id = p.Share_id

where getdate() between p.StartDate and p.EndDate


I assume ph is another derived table or is it short for [Portfolio History]. Also, what does the number 112 mean?
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site