Help needed with complex multi criteria sorting and seacrhing

[johnbert]

I have two tables with data that relates to one another, in one table i have the following headers:

DATE | SYMBOL | TRANSACTION | TOTAL FOR SYMBOL | COMMISSION | PRICE

example of DATA:

2011-09-3 | AAPL | 100 | 200 | 9.95$| 414$
2011-09-3 | VOLVY | 150 | 300 | 7.95$ | 10.75$

The data in this table is all entered manually.

The second table which uses references from the first has the following headers

DATE | TOTAL | CURRENT AAPL | CURRENT VOLVY | ETC....

Example of required DATA:

2011-09-4 | =(200*435$-9.95$)+(300*11.56$-7.95)+ETC | 435$ | 11.56$ | ETC....

The short explanation of what i want to do:

TOTAL (at a given date) = (if the symbol of the column in table 2 = symbol in table 1 AND the date in table 2 is equal or greater then the date in table one for that symbol, then output the total number of shares from table one for that symbol at that date) * (current MKT Price, which is the value in the column in table two with the same name as the symbol found for part one) + [the same statements for the next symbol] + [the same statements for the symbol after that] + etc (for as many times as there are columns in table 2)

Output is a graph of total worth VS time, based on the date and number of shares.


The long explanation of what we want to do:

I am trying to graph the Net Present Value of a stock portfolio, as it varies with time (i.e. like google finance or yahoo finance etc....). Essentially the NPV is based on the number of shares that i have of a given company and the market price of the shares of that company. since the number of shares changes over time as well as the date, and since i dont want to enter all of this manually. as well as the different companies that shares will be purchased from will change over time. i want a way of inputting my transactions i.e. purchasing and selling of shares, and outputting the current value of the sum total of all the shares for a given date.

I tried using DGET but this only can return one value, i.e. if i have multiple transactions in the same day it gets confused. VLOOKUP does not seem to be able to handle multiple criteria. Please help, i've been reading on forums and trying to do this for a couple weeks now and i am completely stuck.

[MarvinP]

Hi johnbert and welcome to the forum,

This seems like a Pivot Table with Running totals answer to me.
http://blog.contextures.com/archives...-pivot-tables/

I know exactly what you are looking forand find many sites give two different kinds of reports.
1. Realized Gains = Stuff you have bought and sold and how much you have lost
2. Unrealized Gains = Stuff you still own and if it is up or down (how far down you are).

If you could supply a short history with a starting portfolio value in dollars and a few transactions it might be easier for us to show what is happening. I found putting I had to put a neg sign on shares if I sold them and plus if I bought them.

Hope this helps.

[johnbert]

Thanks for the reply,

I attached a sheet with a sample of what i want to do

highlighted in red is what i would like excel to automatically calculate

and the graph is the final output i would like to have.

[johnbert]

I came up with a very rough complex solution, but for now it works. Still trying to work out the bugs and refine the code.