Note: As of 1st April 2019, this method no longer works. Might update in the future if I can get it to work again.
One of the key problems with fundamental analysis is that it takes up a lot of time. Even if you know exactly where to look for the data, clicking through multiple web pages just to get a sense of the company you want to analyze will defeat most people. This also prevents most investors from scaling up their analysis. Imagine doing the same task for a portfolio of 100 companies. Worse still, the data on each company gets updated regularly as quarterly reports are published. If you are only one person, how can you have the time to keep up with the fundamental data of a hundred companies?
The trick is extracting the data automatically. The great thing about morningstar.com is that it provides a back-end tool for people to extract the 10 year fundamental data for all the companies on it's website, for free. All you need is a little bit of coding knowledge. I have written up the code for you, so you don't even need to worry about that. You just need to download the software and set it up to to run the code. Follow the steps below, and you will have taken the first step in your journey to invest like a data scientist.
The whole process below will take some time to setup, but once it is done, you will be able to enjoy it's benefits for years to come. This is a basic strategy to shortcut your journey to financial freedom. Take a few difficult steps at the start so that you can make the rest of your journey much easier.
Once you have done the steps below and have the data on hand, it is time to learn how to uncover insights from your new data.
The trick is extracting the data automatically. The great thing about morningstar.com is that it provides a back-end tool for people to extract the 10 year fundamental data for all the companies on it's website, for free. All you need is a little bit of coding knowledge. I have written up the code for you, so you don't even need to worry about that. You just need to download the software and set it up to to run the code. Follow the steps below, and you will have taken the first step in your journey to invest like a data scientist.
The whole process below will take some time to setup, but once it is done, you will be able to enjoy it's benefits for years to come. This is a basic strategy to shortcut your journey to financial freedom. Take a few difficult steps at the start so that you can make the rest of your journey much easier.
Once you have done the steps below and have the data on hand, it is time to learn how to uncover insights from your new data.
How-to guide
The software
The first thing you need to do is install a software called "R". Find the installation link here, it is hosted by our very own National University of Singapore.
Click on "Download R for Windows".
Click on "install R for the first time".
Click on "Download R x.x.x for Windows". If you are looking at this page in the future, a newer version of R might be there. Go ahead and get the latest version. In my experience, all versions of R are backwards compatible.
Once the download is complete, just install it on your computer like any other software.
Once the download is complete, just install it on your computer like any other software.
Now that you have the software, open it up and you should see this.
There is a bit of setup involved here, follow step by step and you should be fine.
Copy this line of code and paste it in R.
install.packages("stringr")
You should see this.
Copy this line of code and paste it in R.
install.packages("stringr")
You should see this.
Press "Enter". It will ask you to select a CRAN mirror.
Scroll to the bottom and select "(other mirrors)". Now a longer list will appear. Scroll and look for the Singapore mirror. If there are two Singapore mirrors, either one should be fine. Once you click "Ok", a short installation process will start. Once it's done, you should see this.
Congratulations! You have just completed the installation of R and set it up for use.
The code and supporting files
First off, create a new folder in your computer. It can be anywhere, but preferably somewhere you can find easily like on your desktop.
Next, download these 2 files and place them in the folder.
Next, download these 2 files and place them in the folder.
| journeyanalytics-all_ratios.r |
| alltickers.csv |
In the R menu, go to File -> Open script....
Look for the folder you just created and placed the 2 files in. Select the file called "journeyanalytics-all_ratios.r. Click on "Open". A new window should pop up with a bunch of code in it. All that code might look scary, but don't worry, I am not going to teach you how to read it. You can drag the window around and make it bigger if you like, so you can see the code inside properly.
Look for the folder you just created and placed the 2 files in. Select the file called "journeyanalytics-all_ratios.r. Click on "Open". A new window should pop up with a bunch of code in it. All that code might look scary, but don't worry, I am not going to teach you how to read it. You can drag the window around and make it bigger if you like, so you can see the code inside properly.
The last step to setup is to change the first line of code. See the part in the quotation marks? That is supposed to point to the new folder you created.
If you created your new folder on your desktop, you should be able to see this in windows explorer.
If you created your new folder on your desktop, you should be able to see this in windows explorer.
Click on the part that says "This PC > Desktop > Journey Investing" and it should give you the directory address of this folder. Copy that and replace the part of the first line of code in R that is between the quotation marks.
Finally, we need to change all the back slashes to forward slashes. This is just a quirk of R. So your final first line of code should look something like this.
Ok, now click on the "save" icon in R, so you don't ever have to do all that again.
The execution!
Here's the fun part. Go back to R and click once in the window with the code, so that your cursor is active in that window. Then using your keyboard press "Ctrl + A" to select all the code. Then press "Ctrl + R" to run all the code. A bunch of stuff will happen in R and it will take about 5 seconds. Once it's done, you should see something like this.
Go back to the new folder you created and you should find a new file called "All Export.csv" created.
If you open this up, you should see 3 lines of data, which contains all the 10 year ratios for BIDU (Baidu), V (Visa) and AAPL (Apple). This data should be exactly the same as the data on the morningstar.com website.
Now if you wish to extract more data for other different companies, open up the "AllTickers.csv" file.
You will notice that BIDU, V and AAPL are the 3 companies I have initially loaded into the excel file. You can simply replace these and add more company tickers underneath them if you want more data. There is no limit to the number of companies you can put here, but the more companies you put, the longer you have to wait.
That's it! Enjoy your new data. If you are extracting less than 100 companies at a time, you can stop reading here. If you are going to be extracting the data of over 1,000 companies, you might want to check out the troubleshooting section below.
Note: When closing R, it will ask if you want to save a "Workspace image". Click on "No" and R will close. Do not save a workspace image, as it will take up a lot of space on your computer.
That's it! Enjoy your new data. If you are extracting less than 100 companies at a time, you can stop reading here. If you are going to be extracting the data of over 1,000 companies, you might want to check out the troubleshooting section below.
Note: When closing R, it will ask if you want to save a "Workspace image". Click on "No" and R will close. Do not save a workspace image, as it will take up a lot of space on your computer.
Troubleshooting
Wrong ticker symbols
The most common error I encounter is the changing of ticker symbols. Since I regularly extract 1,700 companies' data, inevitably some of those companies are going to undergo some changes like delisting, rebranding, be acquired by a new company etc. and these activities will cause the ticker symbols to change. I have placed some error handling into the code for this situation.
When you place an obsolete/wrong ticker symbol in the AllTicker.csv file, what the R program will do is loop infinitely. So there is no way for you to tell if the code is running properly, or if it is stuck looping. What you need to is at regular intervals, like every 5 minutes, go back to R and click on the Esc key on the keyboard. This will stop the program and a new "All Export.csv" file will be exported.
Since you stopped the program prematurely, only a portion of the data will be extracted. You can copy out this data since it has already been extracted. Then go to the AllTicker.csv file and delete those ticker symbols that you already have data of, so the program won't waste time re-extracting data. The program will always go from top to bottom, so you can safely take all the tickers until the last one with data and remove them. Next, check to see if the next ticker symbol you have in your list is still valid, as it could have been running in an infinite loop because of this ticker symbol. Do this check especially if your extracted list of data is very short for the time you gave it to run.
Repeat this process until your list of ticker symbols has been fully extracted. When I do this for 1,700 companies, it takes about 3 hours.
When you place an obsolete/wrong ticker symbol in the AllTicker.csv file, what the R program will do is loop infinitely. So there is no way for you to tell if the code is running properly, or if it is stuck looping. What you need to is at regular intervals, like every 5 minutes, go back to R and click on the Esc key on the keyboard. This will stop the program and a new "All Export.csv" file will be exported.
Since you stopped the program prematurely, only a portion of the data will be extracted. You can copy out this data since it has already been extracted. Then go to the AllTicker.csv file and delete those ticker symbols that you already have data of, so the program won't waste time re-extracting data. The program will always go from top to bottom, so you can safely take all the tickers until the last one with data and remove them. Next, check to see if the next ticker symbol you have in your list is still valid, as it could have been running in an infinite loop because of this ticker symbol. Do this check especially if your extracted list of data is very short for the time you gave it to run.
Repeat this process until your list of ticker symbols has been fully extracted. When I do this for 1,700 companies, it takes about 3 hours.
Forgetting to close the "All Export.csv" file
Every time you run the R program, the All Export.csv file get over-written. So if you want to keep the data you extracted, copy it out to another file. If you leaves All Export.csv open and run the program, R will be unable to export the new data. You will see this.
Do not worry, as your extracted data is still sitting in R. All you need to do is to close the All Export.csv file. Then highlight the last line of code and press "Ctrl + R" again.
The data you have just extracted should then be exported properly into the All Export.csv file.