Make formulas more manageble

[derek davis]

I have taken on a project to create my own investment portfolio. So that I am not overloaded with information, I have created two tabs, DataD and Porfolio. I have attached a sample xls.

DataD is where all the raw data is entered, Porfolio tab is where all the calculation are completed. I feel that I need some assistance with trying to create a set of formulas. I have created an intial set but they seem quite unmanageble. If you take a look on the portfolio sheet and checkout my formulas you will see what I mean. Could anyone suggest an easier and more effeicent way of acheiving the same result.

I have another issue whereby I need to insert rows in the DataD sheet from time to time as and when fresh investments are purchased, and not added to the end of the sheet. This is where the formulas structure falls apart. I am a bit lost on how to make sure the formulas automatically poplate the new row so as to make the the following rows continue to process the correct data which is input via dataD.
Thanks you. Del

 

[AlanSidman]

From my perspective you have a spreadsheet set up which is not normalized. To make it easier for analysis, I would create a spreadsheet for each holding and then have a Master that accumulates the latest data using the vlookup function.

I would build the table for each stock as follows
Date | Shares | Price | Value (Shares x Price)

 

[jayalaxmi]

I have taken on a project to create my own investment portfolio. So that I am not overloaded with information, I have created two tabs, DataD and Porfolio. I have attached a sample xls.

DataD is where all the raw data is entered, Porfolio tab is where all the calculation are completed. I feel that I need some assistance with trying to create a set of formulas. I have created an intial set but they seem quite unmanageble. If you take a look on the portfolio sheet and checkout my formulas you will see what I mean. Could anyone suggest an easier and more effeicent way of acheiving the same result.

I have another issue whereby I need to insert rows in the DataD sheet from time to time as and when fresh investments are purchased, and not added to the end of the sheet. This is where the formulas structure falls apart. I am a bit lost on how to make sure the formulas automatically poplate the new row so as to make the the following rows continue to process the correct data which is input via dataD.
Thanks you. Del

Hi

Please find attached.

Regards
Jaya

 

[derek davis]

From my perspective you have a spreadsheet set up which is not normalized. To make it easier for analysis, I would create a spreadsheet for each holding and then have a Master that accumulates the latest data using the vlookup function.

I would build the table for each stock as follows
Date | Shares | Price | Value (Shares x Price)

Thanks Alan, for your advice, I will look into this, I never used Vlookup before but seems a useful tool..