Syfe Transactions Parser

I did a Syfe REIT+ (100%) Review and decided to open an account. As I was preparing to import the transactions into StocksCafe for tracking over the weekends, I realized an annoying thing when I copy and pasted them into an excel - it is not properly formatted to be displayed in tabular manner -.-

Copy the transactions out

Paste into excel

My thoughts - TMD and spent about 15 minutes to code out a Python (version 2) script to parse and output those transactions into a csv. Regardless if you are using StocksCafe or your own spreadsheet for tracking, you should find it useful.

Firstly, download this script (syfe_parser.py) and save it whenever you want.

Next, copy and paste the transactions and save it as 'transactions.txt' (I hardcoded it, you can modify it if you know how to) in the same place you kept the script.

Last but not least, just run/execute the script (python syfe_parser.py) and you will get a syfe_transactions.csv that looks like this. I even computed the average price per transaction which was missing in Syfe dashboard.

With this, I transform the data further and was able to import all the transactions into StocksCafe! I like to track all my investments with StocksCafe as I get a lot more data/visibility. We can see this portfolio has a projected yield of ~5% which is in line with our goals! Oddly, there are only 19 stocks instead of 20 (iEdge S-REIT 20 Index) in the portfolio and I can't find the components online too.

Updated as Eliezer from Syfe reached out - Came across your post today on the Syfe transactions parser and your question on why there are 19 REITs. Briefly, while the iEdge S-REIT 20 index has 20 REITs, our REIT+ portfolio has 19 REITs because one of the REITs in the index (Manulife US REIT) is USD-denominated. At this point, we are keeping all the REITs in the REIT+ portfolio as SGD-denominated REITs. Even with 19 REITs, you can be assured that our portfolio is still tracking the index very well. Our stated aim is after all to replicate the performance of the iEdge S-REIT 20 index as closely as we can!

Overview

Report

Honestly, I am quite annoyed at how all these robos are not providing an option to export/download these transactions. I have made multiple requests to StashAway (not to Syfe yet) and was always brushed aside that it is not in their list of priorities. How long is it going to take your developer to implement a simple feature like this? Max 1 hour? Oh well, not going to stop me :)

New Syfe customers will have their first $30,000 managed free for 6 months when they use our new referral code (KPOCZM). We will be receiving a $10 cash incentive for our portfolio if you invest $500 or more.

If you are interested in the smart portfolio tracker (StocksCafe) which I am using as shown above, sign up using my link for a longer trial period :) Refer to our Referrals page for more information.

Do like any of the following for the latest update/post!
1. FB Page - KPO and CZM
2. Twitter - KPO and CZM
3. Click here to subscribe using email :)
4. Instagram - KPO_and_CZM (Did you see those delicious food photos to the right --> Unfortunately, you can't see it on mobile.)

StocksCafe API for Stock Data

Note: StocksCafe API is now deprecated and no longer available anymore - updated on 5th July 2020.

Ever since Yahoo abandoned us, we were left stranded without a data source to fetch stock prices. I previously blogged about an alternative - Bye Yahoo Finance! Hi Alpha Vantage! but it has its limitations too. For unknown reasons, sometimes it just doesn't work/return the price of a particular stock code/counter. I suspected that it could be due to the high number of API calls and added additional control such as retrieving the stock price only if we are still owning them but it still wasn't very successful.

As a result, I even modified my google spreadsheet by having a second and third column to re-call the API if it fails. I even have a "Manual" column to fill in the price on my own if all else fails... Yes, it do happen ~.~"

Regular readers will know that we have been using StocksCafe to track the performance of our portfolio and it has released its own API to access stocks data a few months back. I started testing it and found it to be extremely reliable. I no longer need to update the stock price manually! Unfortunately, it is only limited to Friends of StocksCafe (paid users).

Evan's (founder and sole developer of StocksCafe) story is pretty interesting and you can read about it here - The True Cost of Being Free. We are called friends because it started out as a donation to keep StocksCafe alive due to the huge licensing cost.

If there is no chance of you becoming a friend, Alpha Vantage is still the way to go. Otherwise, Friends of StocksCafe or potential Friends, the below steps will be very helpful if you are tracking your portfolio using google spreadsheet too (like us):

1. Go to your profile and take note of your API key. There is a limit on your usage so I do not recommend sharing your key - 100 per day, reset at 12 am SGT. You can see how much we have contributed too :)

2. Go to Script Editor in Google Spreadsheet

Open up the relevant Google spreadsheet, go to "Tools" and click on "Script editor".

3. The API documentation can be found here. If you are in IT, this should be familiar to you and you can do a lot more than what I am going to share. Otherwise, it will probably look very foreign/alien to you, so you can just copy and paste the below code snippet into the newly opened up window/tab.

4. Modify the code slightly by replacing your username, API key, the sheet name and the relevant columns for the function - dailyUpdateStocksCafeColumn() or dailyUpdateStocksCafeColumn2(). Do not modify the rest of the code or do it at your own risk!

5. Go to Current Project's Trigger from Edit.

6. Click on Add Trigger. The project trigger should be empty. Note that the trigger has a 0% error rate.

7. Specify the function you want to run, it should either be dailyUpdateStocksCafeColumn or dailyUpdateStocksCafeColumn2. Next, change the event source to be Time-driven, day timer, the time you will like it to be updated and proceed to save it. You can reference mine below.

What this does is on a daily basis, at the specified time, the function will be called/ran automatically and the specified price column will be updated with the end of date stock price from StocksCafe. Updating the stocks prices once a day will ensure that we are keeping within the limits of the API usage.

As of this moment, StocksCafe has data for SGX, HKEX, KLSE and USX so it is not just limited to SGX stocks data. Evan was kind enough to provide a special discount for KPO and CZM readers! You can sign up using our referral link and see if it suits you first before deciding if you want to contribute.

Current Price:
- Monthly Plan: SGD 4.9 a month
- Annual Plan: SGD 39 a year (SGD 3.25 a month or >30% OFF)
- 7 Years Plan: SGD 215 for 7 years (SGD 2.56 a month or >45% OFF)

KPO Referral Price:
- Monthly Plan: SGD 3.9 a month (~20% OFF)
- Annual Plan: SGD 35 a year (~SGD 2.90 a month or ~40% OFF)
- 7 Years Plan: SGD 195 for 7 years (~SGD 2.32 a month or ~52% OFF)

Benefit: You get an excellent portfolio tracker and I get a follower? lol. If you ever become a "Friend of StocksCafe", I get a 20% commission! At this price point (~$2+ per month - about 2 cups of kopi/teh or $3/4+ per month - 1 cai peng/meal), I think it is a bargain for an intelligent portfolio tracker, access to quality data and many other features.

Anyway, hope this has helped you too :)

Do like any of the following for the latest update/post!
1. FB Page - KPO and CZM
2. Twitter - KPO and CZM
3. Click here to subscribe using email :)
4. Instagram - KPO_and_CZM (Did you see those delicious food photos to the right --> Unfortunately, you can't see it on mobile.)

Bye Yahoo Finance! Hi Alpha Vantage!

Update on 15th July 2020 - It seems that Alpha Vantage is no longer provided SGX data too...

Yahoo Finance decided to discontinued their API services last week and cause a lot of spreadsheets and Excel around the world to break including mine. lol. SGX data is very expensive hence Google Finance does not provide it and others charge a fee for it. Stocks.cafe (formerly SGX cafe) is paying thousands for the licensing fee - The True Cost of Being Free.

Kyith from Investment Moats was kind enough to come out with a workaround by pulling all the SGX stock price from his own site/server - Yahoo Finance Data Shuts Down – My Modification to My Stock Portfolio Tracker. However, that is slightly overkilled (loading 1022 stocks) if I am only interested in a few stocks' price.

While looking online for another alternative for SGX data, I saw people recommending Alpha Vantage and decided to give it a try. It is actually very easy to set up and I also found a code to parse the JSON data which makes my life even easier! So I decided to share a step by step instructions on how to set it up :)

1. Register for an Alpha Vantage API key

Go and Claim your API Key by filling up a simple form. Only first name, last name and email are required. After you have submitted, copy down the provided API key somewhere immediately! They do not send any email and you cannot login to look for it again.

2. Go to Script Editor in Google Spreadsheet

Open up the relevant Google spreadsheet, go to "Tools" and click on "Script editor". Next, copy and paste the below code snippet into the newly opened up window/tab.

You can name the project whatever you want and leave the script name as it is. It should look something like below.

Once you are done, save the project.

### Update on 11-11-2017

Some of you may have encountered a weird bug (getting a 0 instead of the latest price) which I believe is due to Alpha Vantage dropping the requests. I have added a sleep function so that it will not make all the requests at once. On the bright side, the longest it should take is 30 seconds due to limitation from Google Script side (all custom function will fail if it sleeps for longer than 30 seconds).

The function should be called in this way: =getAlphaVantageSlowly(B4, ROW())

3. Call the Function and Get your Price!

Simply call the function to get the latest price! Do note that the stock code format is similar to Yahoo Finance - "ABC.SI".

If you are more technical and can write your own code, Alpha Vantage provides other data as well, do refer to their API documentation for more information.

Hope this has helped you :)

Credits: The above code was found in one of investment moat's comments shared by chris.
Limitations: API call frequency does not extend far beyond ~100 calls per minute - Support