After previous post about data collection, now I would like to share out how to use those data to generate a Basic REITs Analysis Summary through Google Spreadsheets. I will use 3 REITs examples, which are SPH REIT from previous post, First REIT and Ascott Residence Trust.
This summary would require consolidation of a few google spreadsheets that I have shared previously. Below are the post of spreadsheets used, I would suggest you to go through first before you continue this post:
i) How to Create Your Own Basic REITs Screener
ii) Relative Valuation for REITs.
iii) How to DIY a REITs Data Collection Spreadsheet
This summary require more formula work, therefore I have added SREITs Basic Screener as overview page for formula link up. I have added a few more columns as compared to previous version, which are:
i) Shortname - For formula to link up with specific sheet name
ii) Dividend Ex-Month - To indicate rough ex-dividend month, more useful to semi-annual type
iii) Latest Financial Period - Result quarter is by formula; annual report to be updated manually.
iv) Support / Distribution % - To know whether particular REITs come with income support
Here is the link for this google sheet. You could refer below for the overview:
Once you completed particular REIT data collection, you could copy formula from the any of 3 examples and paste into empty cells. Make sure you name the tab according to short name in order for formula to work. If you find any formula showing N/A, try delete and undo it to refresh.
Next, let's look at the analysis summary.
You would need to make a copy so that you could select the REIT counter from cell A1. Once you select, it would summarized all information from particular REIT data page. You could see some graphs where data range is at cells A61 to V72. You could print this 2 pages summary to pdf or hardcopy, I have already set the page break.
Feel free to make a copy, modify it and suit to your own use. Note that I am not a good designer on the layout, so for those who can re-design the summary into nicer layout, please feel free share with me. This is the first google spreadsheet that come with links between different tabs, you may require a bit more time to digest and familiarize. Let me know if you find any error or need any help on this spreadsheet.
One bad news would be, my work which is on project field, is at peak period since 2 months back. This would required me to work long hour weekday and sometimes weekend which expected to last for another 2 months. I would still try to squeeze time for writing, but I couldn't do that on weekly basis as of now. For all the readers, really thanks for your support all the way, if you have anything would like to contact me, I would definitely reply you.
This summary would require consolidation of a few google spreadsheets that I have shared previously. Below are the post of spreadsheets used, I would suggest you to go through first before you continue this post:
i) How to Create Your Own Basic REITs Screener
ii) Relative Valuation for REITs.
iii) How to DIY a REITs Data Collection Spreadsheet
 |
| We have data now, time to make it into information |
i) Shortname - For formula to link up with specific sheet name
ii) Dividend Ex-Month - To indicate rough ex-dividend month, more useful to semi-annual type
iii) Latest Financial Period - Result quarter is by formula; annual report to be updated manually.
iv) Support / Distribution % - To know whether particular REITs come with income support
Here is the link for this google sheet. You could refer below for the overview:
Once you completed particular REIT data collection, you could copy formula from the any of 3 examples and paste into empty cells. Make sure you name the tab according to short name in order for formula to work. If you find any formula showing N/A, try delete and undo it to refresh.
Next, let's look at the analysis summary.
You would need to make a copy so that you could select the REIT counter from cell A1. Once you select, it would summarized all information from particular REIT data page. You could see some graphs where data range is at cells A61 to V72. You could print this 2 pages summary to pdf or hardcopy, I have already set the page break.
Feel free to make a copy, modify it and suit to your own use. Note that I am not a good designer on the layout, so for those who can re-design the summary into nicer layout, please feel free share with me. This is the first google spreadsheet that come with links between different tabs, you may require a bit more time to digest and familiarize. Let me know if you find any error or need any help on this spreadsheet.
 |
| And now, I have a bad news |
Thank you for your selfless sharing of reits knowledge and spreadsheet. Take care and hope to see you back soon.
ReplyDeleteThanks Alex. I am trying to share how I do analysis with google spreadsheet, and for free. If you find it useful, do share with your friends.
DeleteJiayou Vince! :D
ReplyDeleteThanks. Need to take short break due to demanding work schedule
Delete