•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Need formula advice for a personal portfolio management excel.
Our take
Managing a personal portfolio can feel overwhelming, especially when trying to calculate an “as at” valuation across multiple accounts in Excel. You’ve laid a solid foundation with your transactions and price master tabs, but the formula for total account value can be tricky. By leveraging the cumulative quantity of unique assets and their corresponding prices, you can achieve your goal. Let’s explore how to refine your approach and tackle those complex formulas, ensuring your portfolio shines with clarity and accuracy.
Im creating a Finance excel and fear ive bitten off more than i can chew. I have extensive experience with excel but could use advice in one area.
Background of Excel. The document has a 'transactions tab' that manually tracks all financial trades made, by Date, Account, Asset, Quantity (there are more columns not relevent for this). I have sheets for manually input price data for each asset, this culminates in a 'price master' tab containing Asset Date Price table, of all the price table data (note it doesnt contain dates of weekend trading for securities).
The goal was/is to create an "As at" portfolio valuation across all accounts. This sheet contains Date (sequentially down), Account 1, Account 2, Account 3......
I have spent a while trying to create a formula for this as at valuation. It is required to calculate the total account value in each account column as at the row date. To do this, it must first calculate the cumulative quantity of unique each asset (from the transactions table)(row date and all prior dates) multiplying it by that assets price for that date in the price table. The total of these will be the total account value as at that date. (when a date isnt available it should find the last date with a value and use this, this part i have solved). The workbook utilises some heavy formula but this one has stuck me.
I have tried creating a positions table, but had difficulty stopping the end of the sum if when the date of the transaction went past the date it should calculate to. Any help or advice is appreciated, happy to provide extensive additional detail and example formula.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to deal with a bulky spreadsheet that is starting to hit the limits of Excel?Hello all, I have been venturing on quite the Excel journey the past year or so. I made a corporate spreadsheet that is approaching 500k formulas and that is starting to get serious speed issues at this point. It is 2026, so I conversed with ChatGPT several times regarding the speed issue, but realized I am way better off asking the experts here anyways. What is the problem So, my spreadsheet imports flat databases with specific information regarding objects that need further analysing. The imported flat databases run from say A tot CC or something, from which I probably draw about 12-15 datafields that are used for further analysis. It 'may' be more in the future. Afterwards, said data gets 'enriched' (manually) by things that aren't in the database, also because said data needs a human eye that cannot be automated. So far, so good. Right now, each object gets analysed from several different angles. As it stands, my spreadsheet runs from A until NA or something on the Formula Page. Many columns receive data from preceding columns, that are in the turn the result of many (slightly complex) logical IF or IFS tests, many of which are nested 3 or 4 deep. Often, they work in conjunction with X.LOOKUP to retrieve values, as the columns on the formula page are not equal. For example: A until BC on the Formula Page may analyze 150 objects, BD until DD may analyse 100 objects (from the same dataset, so narrower), and so forths. Thus a lot of X.LOOKUP is required, also because the first 'block' comes up with values that need to be found with X.LOOKUP. Also, values need to be retrieved from the flat database 'import' page with X.LOOKUP. Finally, X.LOOKUP is an insurance compared to FILTER, as I am not fully convinced that empty values in the flat database always contain a space (" "). To get to the point I use many IF, IFS, AND, and if need be, OR, formulas. Thinks: tens of thousands, probably in excess of 100k. These are compounded with X.LOOKUP, or X.LOOKUP gets used copiously without those. Here too, think tens of thousands. These formulas are - as much as possible - in array format, even though I find it controversial to do that as I consider how it can create a chain of updates throughout the spreadsheet. 'Dependencies' is the name of the game, with one object receiving many possible alterations / adjustments due to manual input data, for which the spreadsheet needs to provide. Right now, when I update a value, it may take up to 4 seconds to update the spreadsheet, which is already beyond the annoyance point for me. This leads me to these (hopefully) simple questions: Is it smart to use array formulas, knowing that each thing I change should only impact that one object line (for example, row 488) and none other? It is important to mention that object 1 does not influence object 488, or any other. Any manual data field only effects the object in the row it is in. In my mind, array formulas do not make sense in that regard, as it can result in a cascade of updates, but apparantly array formulas are 'way more efficient'. Is use of a VBA library the way to go to reduce lag and create more of an instant spreadsheet again? I am not able to code in VBA yet, but I am in the slow process of learning it regardless. Alternatively: should I use LET whenever a repeated lookup is needed in the same formula? Really looking for to your answers! submitted by /u/EvolvedRevolution [link] [comments]
- Need Excel Help – Investor Distribution Comparison (High Visibility Project)Hi everyone, I’m an accountant in the real estate industry, and I’ve been given a high-visibility project that I really want to knock out of the park. For the past ~2 years, I’ve been manually calculating and distributing investor returns using a QFR-based Excel process. This feeds into our accounting system (Sage Intacct) and ultimately into our ACH distributions. Recently, our company developed new portal functionality that allows investor distributions to be processed automatically with the click of a button. Before this goes live, I’ve been asked to validate it by comparing historical distributions (January & February) against what the portal generates. What I Have So Far My initial thought was to create a basic comparison like: Historical Amount Portal Amount Variance (=B2 - C2) But that feels way too basic for something this important. I want to elevate this into something management can actually gain insight from—not just a simple variance check. What I’m Trying to Build I’d love help or ideas on how to make this spreadsheet more robust and “presentation-ready.” Specifically: 1. Comparison Tab Clean layout comparing historical vs. portal data Meaningful variance analysis (not just raw differences) Flags or indicators for material discrepancies Anything that helps quickly identify issues at a glance 2. Summary / Dashboard Tab High-level view for management Total distributions (historical vs portal) Total variance and % variance Count of mismatches or exceptions Any visual elements (charts, conditional formatting, etc.) that improve clarity 3. Edge Cases / Notes Tab I also need a third tab that outlines nuances the developers need to consider before production, such as: JE import creation requirements Rounding issues Wire vs. ACH needs for custodians Investor-specific scenarios I understand the logic behind these items, but I’m struggling with how to present them in a clean, structured way. My Skill Level I’d say I’m beyond a beginner in Excel, but definitely not advanced—I know the basics well but haven’t fully leveraged things like dashboards, advanced formulas, or more polished presentation techniques. What I’m Looking For Specific formulas or features I should incorporate Layout or structure suggestions Ideas to make this more insightful for management Anything that would make this feel like a “senior-level” deliverable This is a big opportunity for me to stand out, so I really appreciate any advice you can share. Thanks in advance, I'm excited to dive into the community! 🙏 submitted by /u/Affectionate_Net3153 [link] [comments]
- Monthly tracking workbook I use to track employee sales metrics; Trying to find a way to make the process less labour intensiveTruly having a hard to describing my issue effectively but hoping someone can help. First time posting here and I'm by no means an expert with excel, so please be kind! I have a monthly workbook where I track each employees revenue and other metrics. Every 2 weeks for payroll, I provide a print out of these numbers, and the payroll sheet pulls data from multiple sheets in the workbook. For example, every workbook has a separate sheet for each day of the month, titled "1" through "31". I have pay period sheets, so I'll use one titled "04.02.26 - 04.15.26". Then I'll have the data for each employee pulled from multiple sheets. For example, I use the formula =SUM('2:15'!E3) to pull the sales data from each day of that period for the specific employee. This works quite well. However, when I create a new month's spreadsheet, I have to manually alter this formula for each employee and for each data point (more than just revenue, at least 6 different data points for 7 employees). Is there a way to automate this? For example, a cell or two where I'm able to enter the date range and all of the formulas update to that date range for the corresponding pages? I'm sorry of this post is confusing. Truly it's confusing even typing it! submitted by /u/Ok_Smile9222 [link] [comments]
- Working on creating a formula to use information across two cells to determine calculations in other cells(reposted first post got removed) I'm not certain the IF formula(s) are what I need but I'm not sure what else to use. Trying to create a spreadsheet for work: the premise is that if one or two people are the Contact for a project, they will split 5% of the project's earnings, each getting 2.5%; if only one person, they get 5%. The same for if one or two people who are the Winners for the project. I need some way for the spreadsheet to be able to see that if someone's initials are under either Contact or Winner, to then give them either 5% of the net income if they are the only Contact or only Winner, or 2.5% if they share either spot with someone else. The total amount of the net income given out as a bonus will always come to 10%. The first picture shows my 'backend' sheet and a formula I was trying that would calculate 2.5% of the Net Income if someone's initials showed up on the project, but this doesn't work if their initials only show up once because then they would need to get 5%. I would also hope there would be a less clunky way to do this many calculations. The second picture is a section of the main page of the sheet showing the Contact and Winner columns, the Net Income the bonus comes out of, and then the Total C/W amount under everyone's initials that adds up their total bonuses. Backend sheet, 'points' refers to first sheet First sheet of spreadsheet Please let me know if I have not been thorough enough with explaining what I'm trying to do, I'm so deep in this now that I am really really confused and just need help straightening this all out. Using newest version of Excel on a macbook, have also been working on same spreadsheet in Windows. I'm not a beginner at excel but not all that good either. TYSM in advance. submitted by /u/Financial_Device7400 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#Excel compatibility#AI formula generation techniques#financial modeling with spreadsheets#big data management in spreadsheets#conversational data analysis#row zero#real-time data collaboration#intelligent data visualization#rows.com#financial modeling#google sheets#portfolio valuation#excel#account value#financial trades#transactions tab#price master