Before we can do anything with Excel, we need to get some numbers! Some of it we will need to write down, some can be copied and pasted, and some we can download directly as an excel file. First, you will need to get your historical portfolio values from the HTMW website. This will open up a small window showing what your portfolio value was for every day of the contest.

Next, open up a new blank spreadsheet and click cell A1. The column headings should be included too. This will add a new row to the top of the spreadsheet where you can type in the column names. For this example, we want to get the historical prices for a stock so we can look at how the price has been moving over time.

Go to the quotes page and search for S using the old quotes tool the newest version does not yet have historical prices:. For this example, we will use the same dates that we saved for our portfolio values, January 11 through January 15, Congratulations, we have now imported some data into excel!

Notice that your column headings are already detected — this will be important later. First, this data is in the opposite order as our portfolio values. To get it in the same order, we want to sort this table by date, from oldest to newest. You can now choose what we want to sort by, and how to sort it. This is not because there is an error, the number is just too big to fit in the width of our cell. To fix this, we can increase and decrease the widths of our cells by dragging the boundaries between the rows and columns:.

I think that we will only want to use the Adj.

Calculate your stock portfolio performance in excel | Get Digital Help - Microsoft Excel resource

Close will be your new column B, with no more empty space. You now have your historical price data, so save this excel file so we can come back to it later. If you want a copy of your open positions or transaction history in Excel, you can download it directly from HowTheMarketWorks. First, go to your Contests page and find the contest you want the information for. This will download a spreadsheet showing your transaction history, open positions, and your current cash balance with portfolio value.

You might get a warning when opening the file, this is normal. The spreadsheet should look similar to the one above. The top red square is your transaction history, the bottom red square is your Open Positions. To actually use this data, you will need to open a new blank spreadsheet and copy these boxes just like we did above. Select the information in the box above, then paste it in to your blank sheet:. This is the case with the Ticker, Commission, and Total Amount cells.

Columns E and H are now blank, so we can get rid of those too. Getting your open positions will be very similar, but we need to enter the Column Headings in Row 1 ourselves. Open a new blank spreadsheet, and paste in the second box from the file you downloaded from HowTheMarketWorks.

It should look something like this:. Just like with the Transaction History, first unmerge all your cells, then delete the blank columns:. Now everything should move down, and your first row should be blank. Enter these as your column headers:.

Get Stock Quotes in Excel - Excel Clout

We will go over how to make line graphs of your daily portfolio value and your portfolio percentage change, plus a bar chart showing your open positions.

This is usually the most fun part of using excel to track your stock portfolio. First, we want to make a line graph showing our daily portfolio value. First, open your spreadsheet that has your daily portfolio values:. And that is it! Your new chart is ready for display. You can even copy the chart and paste it in to Microsoft Word to make it part of a document, or paste it into an image editor to save it as an image. Next, we want to make a graph showing how much our portfolio has changed every day.

To do this, first we need to actually calculate it. In the next column we will calculate our daily portfolio percentage change. Now we need to make our calculation. To do this, in cell C3 we can do some operations to make the calculation for percentage change. You can use the same symbols you use when writing on paper to write your formulas, but instead of writing each number, you can just select the cells.

To calculate the percentage change we saw between day 1 and day 2, use the formula above in the C3 cell. It should look like this:. Now click on the bottom right corner of that cell and drag it to your last row with data, Excel will automatically copy the formula for each cell:. You now have your percentages! This is because it is trying to show both the total portfolio value and the percentage change at the same time, but they are on a completely different scale!

To correct this, we need to change what data is showing. This is how we decide what data is showing in the graph. Items on the left side will make our lines, items on the right will make up the items that appear on the X axis in this case, our Dates.

A new menu will appear on the right side of the screen. Congratulations, your graph is now finished! You can now easily see which days your portfolio was doing great, and which days you made your losses. Next we would like to make a bar chart showing how much of our current open positions is in each stock, ETF, or Mutual Fund. Since we want to make a bar chart, we can only have two columns of data.

We want one column showing the symbol, and a second column showing how much it is worth. This graph is now finished, but you can also try changing the Chart Type to try to get a Pie Chart. Then select any of the options to change how your pie chart looks. This one should look almost the same as the one you have on the right side of your Open Positions page. To do this, open the spreadsheet with your transaction history. First, we want to change how the data is sorted so we can group all the trades of the same symbol together.

For now, hide those rows. Lets start with the trade for S. This one is easy because the shares I sold equal the shares I bought. This does not work for UWTI, because I sold a different number of shares than I bought. This means that I need to first calculate the total cost of the shares I sold, then I can use that to determine my profit. Now you have your profit or loss for this trade. Create An Account Login. Home Stock Market Game Using HTMW Sign Up! Using Excel to Track Your Stock Portfolio Kevin Smith Beginners , Personal Finance January 19, May 25, assignment.

Using Excel To Track Your Stock Portfolio — Getting Some Data Before we can do anything with Excel, we need to get some numbers! Getting Your Historical Portfolio Values To get your old portfolio values, you can copy and paste them out of the HTMW website..

Getting Historical Prices For Stocks Copy And Pasting Data In To A Spreadsheet For this example, we want to get the historical prices for a stock so we can look at how the price has been moving over time. Go to the quotes page and search for S using the old quotes tool the newest version does not yet have historical prices: Now copy the data, select cell A1 in your blank excel spreadsheet, and paste.

From there, there are few things we would like to change. Changing The Order Of Your Data First, this data is in the opposite order as our portfolio values. Now your data should be in the same order as your portfolio values from earlier.

To fix this, we can increase and decrease the widths of our cells by dragging the boundaries between the rows and columns: Getting Your Transaction History And Open Positions Copying data from another spreadsheet If you want a copy of your open positions or transaction history in Excel, you can download it directly from HowTheMarketWorks. Select the information in the box above, then paste it in to your blank sheet: You can now save this sheet and close it.

Open Positions Getting your open positions will be very similar, but we need to enter the Column Headings in Row 1 ourselves. It should look something like this: Just like with the Transaction History, first unmerge all your cells, then delete the blank columns: Now we need to add our column headers.

To do this, we need to insert a new row. Enter these as your column headers: Now save your spreadsheet for later.

Make a High-Low-Close Stock Market Chart in Excel

Line Graph — Your Daily Portfolio Value First, we want to make a line graph showing our daily portfolio value. First, open your spreadsheet that has your daily portfolio values: Line Graph — Portfolio Percentage Changes Next, we want to make a graph showing how much our portfolio has changed every day. Doing calculations in Excel In the next column we will calculate our daily portfolio percentage change.

It should look like this: Now click on the bottom right corner of that cell and drag it to your last row with data, Excel will automatically copy the formula for each cell: Bar Chart — Seeing Your Open Positions Next we would like to make a bar chart showing how much of our current open positions is in each stock, ETF, or Mutual Fund. First, open your spreadsheet with your Open Positions.

You can see the calculation we used at the top This does not work for UWTI, because I sold a different number of shares than I bought. If reading this article was an Assignment, get all 3 of these questions right to get credit!

Click "Next Question" to start the quiz! Your Open Positions Page. Your Transaction History Page. Your Account Balances Page. Right-Click the chart and click "Select Data". Right-Click the chart and click "Format".

We have received your answers, click "Submit" below to get your score! How The Market Works - Free Stock Market Game. Company Info About Us Advertise With Us Contact Us Advertise With Us Disclaimer.

Stock Info Live Quotes Live Charts Symbol Lookup Top Companies What Is Forex? Resources Articles Books Websites. All information is provided on an "as-is" basis for informational purposes only, and is not intended for actual trading purposes or market advice.

Quote data is delayed at least 15 minutes and is provided by XIGNITE and QuoteMedia. Neither Stock-Trak nor any of its independent data providers are liable for incomplete information, delays, or any actions taken in reliance on information contained herein.