Calculate your stock portfolio performance in excel
Track your stock investments in excel and use a web query to import current stock prices from yahoo.
- Setup excel sheet
- Concatenate stock symbols
- Import current stock share prices using a web query
- Refresh web query to update stock prices
Download excel tutorial file: Track your stock portfolio.xls.
Setup excel sheet
Here is a picture of my excel sheet:
Formula in G2:
copied down as far as needed.
Formula in J2:
copied down as far as needed.
Formula in K2:
copied down as far as needed.
Concatenate stock symbols
We need to concatenate ticker symbols in column B to be able to use them in a web query. I am using this simple user defined function that I have created in a previous post. I am using this udf in cell A1 on sheet2.
Formula in A1 on sheet2:
VBA
Function ConcComma(Substrings As Range) Dim CELL As Range For Each CELL In Substrings.Cells ConcComma = ConcComma & CELL.Value & "," Next CELL ConcComma = Left(ConcComma, Len(ConcComma) - 1) End Function
How to Create Custom User Defined Excel Functions
Import current stock share prices using a web query
- Select Sheet2
- Select cell A3
- Click "Data" tab.
- Click "From Web"
- In the address field type:http://finance.yahoo.com/q/cq?d=v1&s=["Quote","Quote"]
- Click yellow arrow to select the whole table.
- Click Import
- Select where you want to place the imported table (cell A3 on sheet2)
- Enter parameter value "Quote"
- Click "Select cell" symbol
- Click cell A1 on sheet2
- Enable "Use this value/reference for future refreshes"
- Click OK.
Refresh web query to update stock prices
- Click "Data" tab.
- Click "Refresh All"
Download excel sample file for this tutorial.
Remember to enable macros.
Track your stock portfolio.xls
(Excel 97-2003 Workbook *.xls)
Recommended posts
Calculate your stock portfolio performance with Net Asset Value based on units in excel
Related posts:
Calculate your stock portfolio performance with Net Asset Value based on units in excel
Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel
Compare your stock portfolio with S&P500 in excel
Excel udf: Import historical stock prices from yahoo – added features
Recommended categories
This tool works great, until I start inserting more items in row 54 and beyond on sheet 1...then I get an error in Excel noting "bad parameter type. MS Excel is expecting a different kind of value than what was provided." This happens when I update the formula in sheet 2, cell A1 (concatenate) to include anything beneath row 53 in sheet 1. How can I remedy this?
Thanks, Robert
Robert,
I think the connection string (the concatenated string in cell A1, sheet2) can´t exceed 255 characters.
I did not know this when I created this post. I´ll see if I can find a solution.
Hi,
can you please assist us to Import stock data BSE india web site as like yahoo what have did on the above
Many Thanks,
Vijay
Hey Oscar, hey all,
I wanted to let you know that I just finished a project in college around machine learning and stock trading. While it wasn't too successful, I did build a pretty good pipeline for bulk downloading historical stock quotes and quantitative data. I've made it available at http://www.free-stock-data.com and you can use it to download data to your Excel sheets. It's probably a better route if you need long-term or verbose data, and you can download an entire portfolio at once.
Cheers!
Nice work. How would one use excel to create a graph of end of day portfolio valuation over time? For example, enter your portfolio in the spreadsheet you created and then chart the portfolio end of day value from its inception?
Hi Oscar,
I downloaded and used the sample that you've provided. Worked fine till I started getting the same error that Robert has mentioned. I tried saving the file as ".xlsm" in Excel 2k7, but the problem still persists.
You mentioned that you were trying to find a solution. Were you successful?
Pls let us know.
Rgds
Also, is there a possibility of tracking the SELL orders and then tracking the profit/loss?
Sujesh,
Try this file:
Stock-portfolio-v2.xls
Hi Oscar,
Your latest file: stock-portfolio-v2.xls appears to work for much larger number of stocks. Can you explain how you did it?
Also, it appears to take a long time to download. Have you intentionally added delays to avoid Yahoo blocking you out?
Thanks.
- Abs
Abs Raw,
Your latest file: stock-portfolio-v2.xls appears to work for much larger number of stocks. Can you explain how you did it?
I used the user defined function in this blog post:
Excel udf: Import historical stock prices from yahoo
Also, it appears to take a long time to download. Have you intentionally added delays to avoid Yahoo blocking you out?
No, there are no delays. I am guessing it takes longer to download quotes from yahoo.
anyone have a larger copy? i need 50 rows and am not very computer savy
derek,
Use this file: Stock-portfolio-v2.xls
Add your stock ticker in cell B7.
Extend user defined function
1. Select cell range I6:O6
2. Copy cell range (Ctrl +c)
3. Paste (Ctrl + v) to cell range I7:O7
I tried to duplicate the above steps using Excel 2010 and it doesn't seem to be working. Does any of this need to be tweeked for Excel 2010?
I would like to just like to figure out how to get current market prices for about 5 ticker symbols with the ability to refresh the prices whenever I want to. Real simple. I don't know what vba code is or any of the other technical jargon. I'm pretty bright and I'm beyond a beginner on Excel. I just don't know what I'm supposed to copy character for character and what might need some customizing. For example, when I entered this:
=ConcComma(Sheet1!B2:B6) + Enter
(and I copied and pasted that whole thing into the cell)
in A1 on sheet 2 the stock tickers don't transfer over automatically. After entering the above formula the cell shows:
#NAME?
Mark,
=ConcComma(Sheet1!B2:B6) is a custom function. If the vba code is not in your workbook, excel doesn´t recognize the name ConcComma and returns #NAME? error.
How to create custom user defined functions:
http://www.vertex42.com/ExcelArticles/user-defined-functions.html
In Stock-portfolio-v2.xls I understand to add the new Ticker and copy past as it did yeild the results. I don't under stand the top row or what it is pulling, how it's useful etc as it only shows numbers.
Is there a way this can have sub-headings as well and or can they be changed easily with out a big knowing of VB code?
Also Market Value and +/- doesn't seem to have any values pulled.
How can I pull those values too.
Example:
** What are these Headings?
(j)28.25 (k)28.33 (l)27.96 (m)28.00 (n)37667800 (o)28.00 Market value +/-
Thanks, and I appreciate your effort in this thread. I'm a noobe and looking for help with tracking my new found stock portfolio.
Ashton,
** What are these Headings?
(j)28.25 (k)28.33 (l)27.96 (m)28.00 (n)37667800 (o)28.00 Market value +/-
Weird, I don´t know.
See this file:
Stock-portfolio-v3.xls
Oscar,
Thank you so much for your prompt response. I'll be reading what you sent today.
Mark
Thanks works great, i also worked on the similar bases but i used access database, i thought access is faster than excell with handling data and it is ...
I have uploaded the file on http://www.myjournaldb.com/truetrade
hope you guys like it
I am stuck with Market Status but, how can i get the World Stock market status Like Market Open / Closed or going to open in 02:34 or Closed for holiday? any Idea. Thank you!