Hi there,
I am putting together a spreadsheet that pulls stock data from Bloomberg, then crunches it to give me a chart of the profit or loss of an investment strategy. All of that is in Sheet1 of my Workbook, and that much is good!
Now, what I am wondering if anyone can help me with is this: I would like to copy the column in Sheet1 that holds each day's profit or loss (Col Y in Sheet1) to Sheet2, starting in Colunm D on Sheet2. BUT, here are two twists:
1) I'd like to be able to put up to 1,000 "copies" of the profit / loss column in Sheet1 (currently Column Y) into Sheet2 (so copy Col Y in Sheet1 to Col D in Sheet2 for stock ABC, then copy Col Y in Sheet1 to Col E for stock DEF, and so on and so forth). I realize that Sheet2 has only 255 columns, so it seems like each time I copy more than approximately 250 Col Ys from Sheet1 it will be necessary to copy into a new sheet (Sheet2, Sheet3, Sheet4...)
and...
2) I'd like to be able to "square up the data" that is being copied to the output sheets so that all data copied into those sheets (Sheet2, Sheet3, Sheet4...) have the same "last row," regardless of which sheet they are on. In other words, say one stock only has been trading for 1 year and another stock has been trading for 2 years. In my spreadsheet now, the most recent price data and profit and loss data is in the last row of ColY on Sheet1. I'd like to somehow move the data for the stock that has been trading for only 1 year down on Sheet2 when it is copied and pasted such that the most recent data in that stock (the last row) matches the most recent data for the stock that has been trading for 2 years (the last row for that one, too).
The point of all this is to be able to pump a bunch of stocks through Sheet1, then paste the output to Sheet2,3,4... so that we can see how a PORTFOLIO of stocks behaves based on the investment strategy in Sheet1. Once the stock data is lined up on the output sheets, all we have to do is sum across rows to get each day's profit or loss for the entire portfolio!!!
This sounds like a pretty complicated project to me, so I am happy to share any and all of my sheets with any brave soul willing to help. If you are interested in the stock market, I'd be happy to share other ideas with you, too. I'm pretty open.
Thanks,
--Bill
I am putting together a spreadsheet that pulls stock data from Bloomberg, then crunches it to give me a chart of the profit or loss of an investment strategy. All of that is in Sheet1 of my Workbook, and that much is good!
Now, what I am wondering if anyone can help me with is this: I would like to copy the column in Sheet1 that holds each day's profit or loss (Col Y in Sheet1) to Sheet2, starting in Colunm D on Sheet2. BUT, here are two twists:
1) I'd like to be able to put up to 1,000 "copies" of the profit / loss column in Sheet1 (currently Column Y) into Sheet2 (so copy Col Y in Sheet1 to Col D in Sheet2 for stock ABC, then copy Col Y in Sheet1 to Col E for stock DEF, and so on and so forth). I realize that Sheet2 has only 255 columns, so it seems like each time I copy more than approximately 250 Col Ys from Sheet1 it will be necessary to copy into a new sheet (Sheet2, Sheet3, Sheet4...)
and...
2) I'd like to be able to "square up the data" that is being copied to the output sheets so that all data copied into those sheets (Sheet2, Sheet3, Sheet4...) have the same "last row," regardless of which sheet they are on. In other words, say one stock only has been trading for 1 year and another stock has been trading for 2 years. In my spreadsheet now, the most recent price data and profit and loss data is in the last row of ColY on Sheet1. I'd like to somehow move the data for the stock that has been trading for only 1 year down on Sheet2 when it is copied and pasted such that the most recent data in that stock (the last row) matches the most recent data for the stock that has been trading for 2 years (the last row for that one, too).
The point of all this is to be able to pump a bunch of stocks through Sheet1, then paste the output to Sheet2,3,4... so that we can see how a PORTFOLIO of stocks behaves based on the investment strategy in Sheet1. Once the stock data is lined up on the output sheets, all we have to do is sum across rows to get each day's profit or loss for the entire portfolio!!!
This sounds like a pretty complicated project to me, so I am happy to share any and all of my sheets with any brave soul willing to help. If you are interested in the stock market, I'd be happy to share other ideas with you, too. I'm pretty open.
Thanks,
--Bill
