[The VBA Macro is embedded in the Excel sheet, which can be downloaded from the Resources page]
I keep track of my investment portfolio in my own Excel sheet. It's great for customizability and control, but when you have 20 different stocks, it's a hassle to update their latest daily closing price manually. What I used to do, was to go to Yahoo Finance, search for the stock, go to its 'Historical Data' page, download the csv, and copy-paste the prices to my own Excel. One problem was that some stocks are missing prices for certain dates, especially when it's a public holiday in that country. If I just blindly copy-pasted the latest 30 rows of data onto my Excel with a neatly running 30-day date range, I'll end up with wrong closing prices for certain dates. The problem is exacerbated if instead of 30 days I do it with 3 years. What I needed to do, was run a vlookup using the date in my Excel sheet, and perform a non-exact search (=vlookup(date,range,column offset,1))) in the csv file. Then delete the downloaded csv file.
Now imagine doing this once for each of the 20 stocks, every time you want to update the Excel sheet.
I finally decided to bite the bullet and learn a bit of VBA programming, and automated the above. All I need to do now is to enter the date range and the stock tickers (in the same order as they appear in my own Excel records, so that I can copy-paste the entire range over directly), and press Update. The first part of the VBA code clears the working range and generates the range of dates in Column A. The second part of the code converts the start and end date ranges from Excel to Unix dates (which Yahoo uses), throws them and the ticker names into the URL template (thank you Yahoo for standardizing!), and open the file link. It then does a vlookup with the dates in Column A, and saves the result. The csv file is closed without saving.
Now I loop it for all the tickers I'm interested in. Voila!
The code is not the most elegant, and far from efficient, but frankly if you want a polished product there are loads of them out there (some are hidden behind paywalls and memberships though, or have their coding hidden). I wrote this VBA code as a beginner, and it's targeted at beginners. The code is open for all to see and laugh at, and logic behind each line of code is relatively easy to understand (hopefully). Whether you're actually trying to use this macro to update stock prices, or learning to write something similar on your own, I hope this Excel / piece of code will be useful. Have fun!
Comments