How to create a Dynamic Stocks Portfolio using MS Excel and Bloomberg

How to create a Dynamic Stocks Portfolio using MS Excel and Bloomberg

Below are the steps to create a simple dynamic portfolio. You can add more functionalities using Basic Excel functions and formulas. Your only limit is your creativity.

1. Create a simple excel file.
2. Rename Sheet 1 as “My Portfolio”, create a table containing all the stocks you own (or you plan to watch).
3. Open in Bloomberg your preferred stock, example http://www.bloomberg.com/quote/FGEN:PM
4. Copy the URL in step 3.
5. Build your data source in Sheet 2.
   1. Rename Sheet 2 as “Bloomberg data”.
   2. Under the 1st row, type each Stock Code. One code per column.
   3. For your first stock, click/highlight A2 (A1 is the name of the stock). Click DATA >> From Web.
   4. A small browser-like window will pop-up.
   5. Enter the URL from Step2 and press Go.
   6. Click the yellow arrow for the entire site.
   7. Press IMPORT.
   8. Data will be imported into the A2 all the way to A205.
   9. Save your file.
   10. Repeat steps 5.3 to 5.9 for the remaining stocks.
   11. Normally, you will only be interested in the cells that contain the Current price.
6. Go back to Sheet 1 (Portfolio). For each stock, create a cell reference to the Current Price found in Sheet 2.
7. If you want other data, repeat step 6 but change the references in Sheet 2 as necessary.
8. Format your Portfolio tables.
9. To refresh the data, go to Sheet 2 and right click, select REFRESH ALL.