Time Series for Portfolio Book Values

[JapanFreak]

Dear All,

sorry, I need your help again please… The question below builds on a problem earlier today (http://www.access-programmers.co.uk/forums/showthread.php?t=209691). Again I have attached a small sample database which helps to make my problem clearer.

I have a database with a table called tblTrades in which I collect data on stock market transactions:

[COLOR=black][FONT=Arial][FONT=Arial]TradeID – dtDate – sTicker – iQuantity – dPrice[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Arial][FONT=Arial]1 – 01/03/2011 – A – 10 – 50.00[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Arial][FONT=Arial]2 – 01/03/2011 – B – 10 – 25.00[/FONT][/FONT][/COLOR]
[FONT=Arial][COLOR=black][FONT=Arial]…[/FONT][/COLOR][/FONT]

Note:
If iQuantity > 0 it is a Buy, if iQuantity < 0 it is a Sell.


The combination of the following two queries allows me to retrieve the book value of my stock portfolio at a specific date (e.g. 01/04/2011):

[B][COLOR=black][FONT=Arial][FONT=Arial]qryPortfolioBV[/FONT][/FONT][/COLOR][/B]
[COLOR=black][FONT=Arial][FONT=Arial]SELECT Sum(T.iQuantity*T.dPrice) AS dBV[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Arial][FONT=Arial]FROM qryPortfolioHoldingsDate AS Q LEFT JOIN tblTrades AS T ON Q.sTicker=T.sTicker[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Arial][FONT=Arial]WHERE T.dtDate<=#1/4/2011#;[/FONT][/FONT][/COLOR]
 
[B][COLOR=black][FONT=Arial][FONT=Arial]qryPortfolioHoldingsDate[/FONT][/FONT][/COLOR][/B]
[COLOR=black][FONT=Arial][FONT=Arial]SELECT T.sTicker, sum(T.iQuantity) AS iPosition[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Arial][FONT=Arial]FROM tblTrades AS T[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Arial][FONT=Arial]WHERE dtDate<=#1/4/2011#[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Arial][FONT=Arial]GROUP BY T.sTicker[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Arial][FONT=Arial]HAVING sum(T.iQuantity)>0;[/FONT][/FONT][/COLOR]

Note:
If sum(T.iQuantity) > 0 the trade is still open, i.e. the stock is part of the portfolio
If sum(T.iQuantity) = 0 the trade has been closed, i.e. the stock is no longer part of the portfolio


Now I am looking for a way to formulate a query which gives me a time series of the book values of my portfolio over time.

Actually, some time ago user Bilbo helped me with a related problem (http://www.access-programmers.co.uk/forums/showthread.php?t=134869) but I struggle to adjust his suggestion to my current problem.

Is there anybody who can help me here?

Thanks for your help –

JapanFreak

 

[gemma-the-husky]

what time series are you trying to map? daily values, or values within a day?

i think you need a graphing function - but you also need to store time-based data that will enable you to construct the graph.

I am sure all the financial web-sites must maintain enormous databases with that data - or maybe they get it from some third-party.

How do you get it? Do you enter the data, or scrape it from some web-site?

 

[JapanFreak]

Hi again,

thanks for your reply. Please let me clarify my question.

All the data I need to get the data I need is contained in tblTrades, i.e. I don't need any further data. In this table I record all my stock market transactions, i.e. each time when I buy or sell a stock an entry is made.

What my queries so far do is to calculate what I have actually paid for all the stocks which are in the portfolio at a certain point in time, i.e. the book value of the portfolio.*

What I would like to have is a query that gives me this book value / the original cost of my portfolio not only for a certain point in time but as a time series, i.e. for each trade date (i.e. all the dates which are contained in tblTrades).

I hope that helps to clarify my problem.

Thanks

JapanFreak

*) This amount might be higher or (hopefully) lower than what the portfolio is actually worth today.

 

[gemma-the-husky]

I thnik, I might have another table, with say these fields

valuation date
no of stocks
stock cost
stock value
and anything else you might need.



and have a procedure that evaluates these values and saves them in this table, at a particular point in time.

I would probably do this daily - but you could easily reconstruct this table historically, with a loop that iterates a date range, values your portfolio, and saves the caclulated figures.

I doubt if the update would take so long.

maybe some duplication in strict dbs terms, but worth it for this, I would think (as long as you don't change stuff after the event!)


-----
in fact, thinking again, you might want a daily entry per stock. all you get is more rows, and unless you have thousands of stocks, you still are not going to get unmanageable record quantities. And then you can compare parts of your portfolio, say market sectors, or even individual stocks, rather than just the whole thing.

 

[JapanFreak]

I see, thanks for your thoughts and suggestions.

But there is no possibility to do it with a query?