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.
| Author |
Topic |
|
MCCCLXXXV
Starting Member
10 Posts |
 Posted - 2009-05-13 : 15:04:23
|
I'm wondering if someone can give me some advice on the best way to design a database that will act as a stock portfolio.Currently, I keep track of our stock holdings in excel spreadsheets. I have one for each end-of-day. So for example, I have files called "portfolios 5-11-09.xls","portfolios 5-12-09.xls","portfolios 5-13-09.xls"... Each one is structured as follows:-there is 1 tab for each portfolio (6 portfolios total named A through F)-on each tab, there are 16 with data like: ticker, name, shares, cost, market cap, beta, weight....I want to assume that all data for each stock can change from one day to the next. So, for example, a company can change its name or its price will change....even tickers can change. The unique ID will be the company's CUSIP, which is just a standardized way to identify a company's stock.So, I'm wondering what the best way to attack this is. should i just make 1 huge table that looks something like this:Date Portfolio Cusip Name Price5/10/09 A gh867 Micro Inc 12.005/10/09 A uj764 Pico Corp 29.005/10/09 A ik008 Milli Inc 35.005/10/09 B vn854 Apple Inc 55.005/10/09 B gm900 Pear Corp 98.005/10/09 C qs475 Blue Corp 52.005/10/09 C jk999 Green Inc 45.005/11/09 A gh867 Micro Inc 13.005/11/09 A uj764 Pico Corp 30.005/11/09 A ik008 Milli Inc 36.005/11/09 B gm900 Pear Corp 99.005/11/09 C qs475 Blue Corp 52.005/11/09 C jk999 Green Inc 45.005/11/09 C vn854 Apple Inc 56.00 Above, I've shown 3 portfolios on 2 dates. PortA has 3 stocks on both dates. PortB had 2 stocks, but 1 of them moved over to PortC on the next day. Prices changed from day to day.I'm just wondering if this is a terrible idea and if there is some other way to do it. Also, what if I made a new table every day for every portfolio? So there would be 6 tables per date, times 3 years of past history. Is that just a dumb idea? I'm a noob at SQL programming. Thanks in advance for your help. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-05-15 : 21:32:33
|
| That is a not the best approach. Relation databases kind of revolve around the theory of storing everything only once and certainly any solution where you dynamically generate tables is wrong. Your approach also has the downside of allowing data integrity to be compromised (e.g. there is nothing to stop a single CUSIP having a different name or price in the approach above and you can just slap any old portfolio code in there).If I get you right I would have the followingStock(CUSIP, date, ticker, description, price etc) (cusip/date is PK)Portfolio(code) (make sure you can only have data in portfolios that exist)StockInPortfolio(Date, portfolioCode, CUSIP) (all columns are pk)You would then join it all up for a given date.If you wanted to do it even better, you could assume that ticker & company name do not actually change much but the price does, so split that up into 2 tables according to that. You would then have to get the stock data where max(date)<=your date for each stock and the price for the given day (or the oldest max as before if you want to cover non-trading days) |
 |
|
|
|
|
|
|
|
Keeping track of a stock portfolio