I have been trying to simplify my monthly reporting process on a portfolio's performance...The transactions tab has hundreds of individual transactions, many times buying/selling same security. A security can be also bought once but sold in partial lots over different dates. Is there any way to use VLOOKUP to return an aggregate gain/loss by each security into a summary tab? Each security has a unique ID (Symbol) - but given multiple transactions - appears many times...If there is any way to apply a SUM or AVERAGE to get the summary data - would be awesome. Attached is a sample file.
Any help appreaciated!
Like so, some formulas can reference the whole column, others need a more defined range to work efficiently:
Last edited by JBeaucaire; 07-19-2011 at 03:01 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
hi there - I tried to launch an attachment linked to the reply post to my threat - but it seems invalid...?
Reload this thread and try again.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thank u so much - seems to address my primary needs for now...Quick question - how do the different formulas for the Buy (earliest) date vs. Sell (latest) date work? Does the reference data have to be always sorted in chronological order for them to work?? Thanks again
Columns B & C
INDEX/MATCH is a standard lookup formula. The MATCH() formula finds the first instance of the searched value and returns a numeric position back to the indexed range.
Column D, F & I
A standard SUMIF() that is summing all the values in a specific column based on spotting the rows that match the symbol in column A.
Column E & H
Another standard SUMIF() adding all the values in the specified column after matching the column A symbol, then dividing that sum by the number of entries overall using a COUNTIF(). It's a "manual average" if you will.
Column G
One of my favorite "trick" formulas. Your last "sell date" isn't always on the last row with a particular symbol, many of the "last" entries are blank in the sell column. This formula is making an array list of all the rows that match the column A symbol and are greater than zero in the sell date column. Then the formula decides which one is the entry furthest down on the sheet and returns that date from column H.
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thank you for elaborating on those formulas. I had 1 more question related to my needs - is there a way to specify the unique symbol and have ALL of its individual references returned/displayed in another sheet; Eg - for ABT - return all SELL transactions?
Thanks again your help.
You can turn on the AUTOFILTER for that data set and filter the data inplace by any value(s) you want. Once filtered, you would be looking at all the ABT rows, for example, all the others hidden. You can edit/print as needed, then filter a different way or turn it off.
No need for separate sheets, but if you really want one, once filtered, you can copy all visible rows to another sheet.
Last edited by JBeaucaire; 07-20-2011 at 08:08 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
What I had in mind was to list top 10 holdings in a separate sheet and have some type of VLOOKUP retrieve all of its individual transactions; For ex - list, ABT, JNJ, DGICA in a summary sheet - and perhaps have that sheet be auto-expanded and populated from the Transactions tab with all sales (incl. partial) for each stock...Is that possible?? The filter works manually each time...
yes, a manual autofilter/copytoanothersheet can just as easily be a vba-based autofilter/copytoanothersheet/repeatformultiplecriteria kind of thing.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hi there again - was tied up w/ another project but wanted to complete the loop on this...So is there a programming code that can be suggested to create such reporting vs. using an Autofillter manually each time? In my actual portfolio - I have hundreds of securities and the portfolio manager sometimes wants to view 10 disparate ones in terms of entire history / performance etc. Ideally - if I could specify within a new sheet 10 securities - those details would be extracted from the full Transactions tab...
Thanks again!!
Sure that would work. Set up your sheet where you list securities, and then put some securities in there and then manually mockup what a "report" would look like based on that.
It's remarkably simple to record a macro to Autofilter a sheet based on specific criteria and paste the results elsewhere. Adding a "loop" to do multiple filters is not much more work. The real question is always what your data/criteria/final report(s) look like.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks