Not too long ago, I signed up for the SYFE robo-advisor’s REIT+ portfolio (tracking SGX iEdge S-REIT 20 index) and invested a little money. I have yet to post my review of SYFE robo-advisor because I don’t think I’m ready for it yet. (no referral code for you haha) No rush for such things as many bloggers have written on it and there is enough information out floating there.
Having said that, I’m thinking of how else I can value-add and provide little useful resources in addition to what has been said and written. Just yesterday, KPO came up with an excellent blog update on the SYFE transaction parser. I think he updated his blog three times in two days and it pushed the post downwards.
The pain-point came about because robo-advisors typically don’t provide any feature or functionality that allows users to export transactions.
Just to be clear, I don’t want to be stealing his thunder and this resource is fully credited to KPO. Therefore, you won’t find the actual parser script on this blog and I’m sending traffic to his post instead.
I realized that for some of you who needed a little more hand-holding, perhaps I am able to add on to what KPO has done and write a little bit more on the complete process of how I exported SYFE transactions, massaged the data (installation hiccups, Excel vlookup, little data quirks) and imported them into my StocksCafe account for detailed analysis.
Let’s see what happened when I followed his blog post and how I navigated around a few nagging issues. The result is this how-to guide that hopefully will be of some value to you.
Export transactions from SYFE
First, I logged into my SYFE platform and selected my portfolio. Simply highlight and drag all the way down to select all your transactions and save them as a text file. Hehe. I named my portfolio Yield Cushion and that was inspired by Millenial Revolution’s book : Quit Like A Millionaire. Awesome book from the perspective of an engineer, I would like to add!
KPO recommends saving them as transactions.txt because that was hardcoded into his Python script, so why not make our life easier and go ahead and save it using that file name. You can use Notepad or any text editor to do it.
Obviously, that looks crappy and that’s the whole point of KPO’s SYFE parser – to parse the data into something more “readable”. On to our next step which is to install Python.
Install Python and dependencies
The way code works is that you need install the necessary software that’s required before the code can run. Go on and install the newest version of Python (mine is verion 3.7 installed some time back but it doesn’t matter).
Now, in the folder that you have saved the transactions.txt file, download KPO’s Python script titled syfe_parser.py and save it into the same folder. Assuming that Python has been successfully installed, all you need to do to navigate to the folder using command prompt (Google if you’re not sure how) and follow KPO’s instruction by typing python syfe_parser.py and pressing enter.
Well, if you encountered an error message like me which said that it is lacking the necessary dependencies (the start of his code indicated he imported the pandas library), I fixed it by running :
- pip install wheel
- pip install pandas
After a minute or so of auto-installations, running the command python syfe_parser.py will now generate syfe_transactions.csv file that looks pretty. I removed the transfers and bonuses entries because I only wanted pure transactions. One thing to highlight is that awesome KPO has included the computed price for us! Amazing!
StocksCafe import file format and data massage
KPO ended his post by saying he imported the data into StocksCafe thereafter which is nice! OK – if you needed an extra bit of hand-holding, here goes. Know that StocksCafe handle imports of data is a specific way. You have to speak the machine language that it understands or else it will ignore what you’re trying to do.
Lifted from StocksCafe word-for-word :
- Column 1) (-1 or 1 or 0 to indicate sell for -1, buy for 1 and fees for 0) OR (Buy or Sell or Fees text are also acceptable)
- Column 2) Exchange Code (i.e. SGX or HKEX)
- Column 3) Symbol
- Column 4) Units Purchased or Sold
- Column 5) Currency
- Column 6) Price Paid or Received (based on the currency this stock trades in)
- Column 7) Date of transaction (YYYY-MM-DD or MM/DD/YYY)
- Column 8) Total after fees (Blank is allowed) (based on the currency this stock trades in)
- Column 9) Anything you would like to note about the transaction (Blank is allowed)
Example: 1, SGX, ES3, 1000, SGD, 3, 2015-01-03, 3003, “First trade of the year”
Now, what we need to do it to transform our syfe_transactions.csv into the format that StocksCafe needs. Most of it is fairly easy and just a matter of moving columns around – but you would end up with the Symbol column that requires a tiny bit of special handling. This is because the export gives you the REIT name in full whereas StocksCafe requires the stock code.
What I did was to perform a one-time listing of REITs code into my Excel sheet and then perform a vlookup to map the REIT name to the correct code.
If you need my vlookup sample, my C2 cell is this : =VLOOKUP(K2,$O$2:$P$21,2,FALSE).
- value – The value to look for in the first column of a table.
- table – The table from which to retrieve a value.
- col_index – The column in the table from which to retrieve a value.
- range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.
It is basically looking for the REIT name in K2 from my mapping table ($O$2:$P$21) and picking out the correct stock code from column #2 from my mapping table when it hits an exact match. The $ sign ensures that the cell-reference remains static because my listing table location doesn’t change. When you copy and paste the formula into C3, it will look for the REIT name in K3 etc, and so on.
Once you have reached this stage, the last step is to create a new csv file to be imported into StocksCafe. I simply selected the first nine columns and copied-pasted it into a text editor. Since the default Excel separator is <tab>, I replaced all <tab> with “,” and my final import file is done. Click on upload and we can see the result.
Hopefully you have made it this far and successfully imported your SYFE transactions into StocksCafe!
If you have no idea what StocksCafe is, go ahead and read my older posts that I have mentioned it. StocksCafe is basically a suite of portfolio management tools that help me to invest better. Most importantly, it is the last day left to join my StocksCafe giveaway!
I might have missed out little details but regardless, do leave comment if you need any help. Lastly, a big shout-out to KPO for his SYFE parser script and do check out his blog! Maybe I should do something similar to AutoWealth – sigh.