Hi Folks,
I have a problem to solve.
I have a spreadsheet that selects two funds based upon an algorithm that is applied to a basket of funds. At the close of the last trading day of the month, the new fund selections are made (sometimes they change, sometimes they remain the same depending upon the quantities evaluated by the algorithms). I set up the spreadsheet in a way that assumes that at the end of the day of each month, both funds are sold, and immediately after that, the proceeds of these sales are used to buy the two funds for the next month in a 50/50 ratio (that is what Cells D3 and F3 specify). I will try to post a sample of the spreadsheet. Here is the description:
Column A: Dates
Column B: Stock Symbol of First Fund Selected
Column C: Stock Symbol of Second Fund Selected
Columns D and E: Daily adjusted closing prices for First Fund [Column B]
Columns F and G: Daily adjusted closing prices for Second Fund [Column C]
Column H: # of Shares of First Fund
Column I: # of Shares of Second Fund
Column J: Total value of shares in First Fund
Column K: Total value of shares in Second Fund
Column L: Sum of total values of shares in First and Second Fund
Each month, the fund prices shift from one column to the adjacent column (e.g., Column D to Column E). This was to allow me to calculate the closing prices for the month for the two funds, then to calculate the opening prices for the next month for the next two funds selected. You will see that there are calculations in Columns H thru L, and that these are intended to keep track of the daily values of each fund (Columns J and K) and the sum of the two funds (Column L).
Here's what I would like to do:
1. Create a way to automatically calculate all values in Columns H through L. That's the most important issue, since this is the step that is the most labor intensive right now.
2. Create a way to direct Excel to post the correct prices in Columns D thru G based on what fund is specified in Columns B and C.
3. Perform these for the each entire month, then when the new month arrives, repeat these calculations for the new funds selected.
Again, I have a small sample of the spreadsheet showing the above described columns, and will try to post that. But right now, I am not sure how to post attachments. Hopefully I will figure that out in a bit.
Thanks so much,
Terence
I have a problem to solve.
I have a spreadsheet that selects two funds based upon an algorithm that is applied to a basket of funds. At the close of the last trading day of the month, the new fund selections are made (sometimes they change, sometimes they remain the same depending upon the quantities evaluated by the algorithms). I set up the spreadsheet in a way that assumes that at the end of the day of each month, both funds are sold, and immediately after that, the proceeds of these sales are used to buy the two funds for the next month in a 50/50 ratio (that is what Cells D3 and F3 specify). I will try to post a sample of the spreadsheet. Here is the description:
Column A: Dates
Column B: Stock Symbol of First Fund Selected
Column C: Stock Symbol of Second Fund Selected
Columns D and E: Daily adjusted closing prices for First Fund [Column B]
Columns F and G: Daily adjusted closing prices for Second Fund [Column C]
Column H: # of Shares of First Fund
Column I: # of Shares of Second Fund
Column J: Total value of shares in First Fund
Column K: Total value of shares in Second Fund
Column L: Sum of total values of shares in First and Second Fund
Each month, the fund prices shift from one column to the adjacent column (e.g., Column D to Column E). This was to allow me to calculate the closing prices for the month for the two funds, then to calculate the opening prices for the next month for the next two funds selected. You will see that there are calculations in Columns H thru L, and that these are intended to keep track of the daily values of each fund (Columns J and K) and the sum of the two funds (Column L).
Here's what I would like to do:
1. Create a way to automatically calculate all values in Columns H through L. That's the most important issue, since this is the step that is the most labor intensive right now.
2. Create a way to direct Excel to post the correct prices in Columns D thru G based on what fund is specified in Columns B and C.
3. Perform these for the each entire month, then when the new month arrives, repeat these calculations for the new funds selected.
Again, I have a small sample of the spreadsheet showing the above described columns, and will try to post that. But right now, I am not sure how to post attachments. Hopefully I will figure that out in a bit.
Thanks so much,
Terence
