Creating Time Series for Portfolio Values

[JapanFreak]

Dear All,

once again I need your help, I have no idea how to tackle the following problem. I am taking records of stock market transactions. As a final result I want to have a query which gives me for every end of the day the total value of my portfolio.
Therefore I have two tables. The first one, called tblTransactions with columns - among others - Date, Ticker, Quantity, Price does contain my transactions. The second one, called tblQuotes with columns Date, Ticker, LastPrice contains prices for each security traded for every day.
The following SQL code gives me the portfolio holdings with the respective LastPrices on an arbitrarily chosen date (03/09/07):

SELECT T.Ticker, sum(T.Qty) AS TotQty, Q.qCl AS [Last Price], (T.Qty*Q.qCl) AS [SubTotalValue]
FROM tblTransactions AS T INNER JOIN tblQuotes AS Q ON T.Ticker=Q.qTicker
WHERE T.Date<=#3/9/2007# And Q.qDate=#3/9/2007#
GROUP BY T.Ticker, Q.qCl, (T.Qty*Q.qCl)
HAVING sum(T.Qty) >0
ORDER BY T.Ticker;

This works so far. However, now I struggle with the next step. I want to have a query, which for each date (I could use the date column of tblQuotes) adds all SubTotalValues, i.e. gives me the total value of my portfolio for each day.

Could you give me please some hints on how to proceed from here? I would be very pleased if somebody could help me with that question.

Best regards
JapanFreak

[Bilbo_Baggins_Esq]

If you can post a sample, I'd be happy to look at this.

I'm thinking a couple of crosstabs with another select query to bring holdings and values together might do the trick.

[JapanFreak]

Bilbo_Baggins_Esq,

I am of course more than happy to upload an example. The attachment contains the two tables tblTransactions and tblQuotes (the latter one is updated via internet using a free tool called Quaccess -> www.quaccess.com), i.e. the structure of the latter table must not be changed. Additionally the file contains one query, which is the one posted before. It shows the portfolio composition on one arbitrarily chosen date based on the transactions recorded in tblTransactions.

The data are of course only some sample data, which make as such no sense at all. However, they show the basic cases, which are possible: Securities may be bought and sold at irregular dates. The composition of the portfolio (i.e. the securities - identified by tickers - within the portfolio) and the number of stocks held of one ticker can differ every date. tblQuotes contains security prices for each trading day (i.e. excluding weekends and non-trading days like some holidays).

What I like to have is a query, which shows me a time series of the total value of my portfolio for any time period I choose.

I hope this explanation helps so far and is sufficient. I am of course happy to answer any further question that might arise.

Thank you very much for your help.

Best regards,
JapanFreak

[Bilbo_Baggins_Esq]

Here you go...

I left your original qryPortfolioHoldingsWithQuotes2 alone, but made a new query called "qryPortfolioHoldingsWithQuotes".

From it, I derived the final query "qryPortfolioValue".
There you'll find a portfolio value for every date contained in tblQuotes

The query assumes you will have a valid quote for every stock in your portfolio for every day you wish to have a total. In other words, if there is one day that does not have a quote on a particular stock in the protfolio for that day, it will be excluded from the total portfolio value.

[JapanFreak]

Hi,

thank you very much for your help! This is really the best forum of the world.

Best regards,
JapanFreak