GuruFocus Excel Add-in's GURUG function allows you to pull specific data about a guru's portfolio.
Installing GURUF / GURUG
Before you can use either user-defined function, you must first install the GuruFocus Excel Add-in. Please consult the Excel Add-in and GURUF tutorial for instructions.
The GURUG function
GURUG allows you to retrieve a specific guru's trade data as of a specific date. Once you successfully install the user-defined functions, please click on the “Insert GURUF” button either in the GuruFocus Ribbon or the Task Pane.
Click on the "Gurus Data" tab in the "Insert Formula" pop-up to open the GURUG input parameters.
The GURUG function requires the following parameters:
- Guru: Please enter the name of the desired guru, e.g., Warren Buffett (Trades, Portfolio) or Seth Klarman (Trades, Portfolio). You can also enter the guru's ID: for example, the Berkshire Hathaway Inc. (BRK.A, Financial)(BRK.B, Financial) CEO has Guru ID #7.
- Action: This parameter allows the following values- add, buy, sell and reduce. You can also select "Portfolio" to view a guru's portfolio for a specific quarter.
- Start Date: This parameter works like GURUF's parameter. Please enter a full date: For example, enter "2017-01-01" to retrieve all trades after January 2017.
- Display Items: This parameter allows you to output specific columns. Leave this parameter blank if you wish to retrieve the entire trade output. The column headings are…
- Symbol: the company's stock ticker symbol.
- Company: the company's name.
- Price: the company's current share price.
- RecmDate: the record date.
- Trans_share: the impact to portfolio.
- RecmPrice: the average share price for the quarter.
- Price_min: the lowest share price for the quarter.
- Price_max: the highest share price for the quarter.
- Change: the price change from average.
- Share_current: the number of shares that guru held as of the record date.
- Show caption: Enter “Y” to show the column headings or “N” to not show the column headings.
- Transpose and Sort Order work just like their GURUF counterparts: the former controls whether the data is presented horizontally or vertically while the latter controls whether the data is presented in ascending or descending order by record date. By default, the trades are ordered by date DESC, i.e., the most-recent trades are listed toward the top.
Do not confuse GURUG with GURUF
As illustrated in the image above, GURUG has a different set of parameters than its cousin GURUF. Please make sure you enter guru names when using GURUG and company names when using GURUF. Note that the “F” in “GURUF” stands for “financials” while the second “G” in “GURUG” stands for “gurus.” You will not get the desired result if you attempt to enter any of the following:
=GURUF(“Warren buffett”,”Add”,”2017-01-01”)
=GURUG(“AAPL”,”Revenue”,”2017:”)
You can also use GURUG in Google Sheets
To use the GURUG function in Google Sheets, first install Google Sheets as instructed in the following quick start guide. After you select "Insert Formula" in the Task Pane, select the "Gurus Data" tab to insert a GURUG formula.
