Practical aspects of early retirement FIRE movement in Russia

Early retirement has been a hot topic lately. This is partly due to the FIRE movement: “financial independence and early retirement” - to earn more, spend less, and early to throw off the shackles of work.

It would seem that where does FIRE and habr.com? And despite the fact that it is impossible to achieve this early pension without and without taking into account its own diversified assets. And as the long-term practice has shown, there are practically no adequate tools for accounting for stocks, bonds, real estate, coins, and currencies, coupled with country diversification, in one window. In a small background , he said that if you have something to consider, then accounting itself can take unreasonably long time. But here site parsing comes to the rescue and this takes the management accounting of its own portfolio to a new level and, of course, brings early retirement closer.

Important note: this article is for a beginner early retirement and a novice parser :). My experience involves the use of data from sites for personal purposes only. I will touch on the use of Microsoft Excel and Google Sheets, and very little KMyMoney. I'll start in the order in which I myself came across all the products:

Part 1. KMyMoney

Adding a new asset to KMyMoney

There is little information about this program on the Internet and especially little about its components of price parsing. But in investments it is very important to have an actual picture before your eyes.

Configuring the parser in KMyMoney

It is also curious that from the version to the version of KMyMoney (I used the program for several years) some broken links wander in the parser settings, which are almost impossible to use and there is no clear description of the component.

Instruction to use a parsing template for a specific paper

For example, I propose a federal loan bond (OFZ) - these are ruble bonds issued by the Ministry of Finance of the Russian Federation. Data on these bonds can be viewed on many sites or through the Moscow Exchange API . I’ll take OFZ-PK 29012 (SU29012RMFS0), and I’ll look at the price on the website of one of the brokers. The identifier will not be the security code from the exchange, but the internal identifier from the site. This is how the window with the already downloaded quote of the previous day looks like:

The result of the parser for paper

At one time , I created similar patterns for parsing using KMyMoney for many assets for different sites - I hope this will be useful.

Of course, KMyMoney allows you to automatically update the rates of any assets and this is probably the only program that allows you to do this, but like all similar accounting programs, it has certain disadvantages. Namely: the annoying “accounting search” of converging cents (I need a review tool) and a huge amount of mouse clicks (nothing can be done with the investment in two clicks). But this program is probably the best if you want to have an account of personal finances and investments in one window and at the same time have automatic updating of the prices of any assets.

Part 2. Microsoft Excel

There are no built-in functions for parsing in Excel and therefore used VBA based on examples found on the Internet (he himself didn’t really understand at that time to write from scratch) - the code was probably not the most concise, but it performed its functions:

Code Editor in Microsoft Excel

Public Function FinamPriceBondsCorporate(Optional ByVal ISIN) As Double '//  Dim sURI As String Dim oHttp As Object Dim htmlcode, outstr As String Dim Num As Double '//   sURI = "https://bonds.finam.ru/issue/" & ISIN & "/default.asp?resultsType=5" On Error Resume Next Set oHttp = CreateObject("MSXML2.XMLHTTP") If Err.Number <> 0 Then Set oHttp = CreateObject("MSXML.XMLHTTPRequest") End If On Error GoTo 0 If oHttp Is Nothing Then Exit Function End If oHttp.Open "GET", sURI, False oHttp.Send htmlcode = oHttp.responseText '// regex! outstr = Mid(htmlcode, InStr(1, htmlcode, "lose:</td>") + 57, 6) '//    Set oHttp = Nothing '//  html ,   outstr = Replace(outstr, "&", "") outstr = Replace(outstr, "n", "") outstr = Replace(outstr, "b", "") outstr = Replace(outstr, "s", "") outstr = Replace(outstr, "p", "") outstr = Replace(outstr, ";", "") outstr = Replace(outstr, "-", "0") Num = CDbl(outstr) '//   If Num = 0 Then '//      outstr = Mid(htmlcode, InStr(1, htmlcode, "Bid:</td>") + 54, 6) '//    outstr = Replace(outstr, "&", "") outstr = Replace(outstr, "n", "") outstr = Replace(outstr, "b", "") outstr = Replace(outstr, "s", "") outstr = Replace(outstr, "p", "") outstr = Replace(outstr, ";", "") outstr = Replace(outstr, "-", "0") Num = CDbl(outstr) '//   End If FinamPriceBondsCorporate = Num Exit Function ErrorHandler: '  FinamPriceBondsCorporate = 0 Err.Clear End Function
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

In order to use the code, in Excel itself you need to call this, just written, FinamPriceBondsCorporate function:

Excel and using the additional parsing function

The table in Excel (and the accounting template based on it) remained a convenient local option if I wanted to open it and see something in it. But asset quotes could not be updated without human intervention, and even more so, this table could not send a report on Monday morning and even create events in the calendar of payments for the week ahead. In general, I had to move on.

Part 3. Google Sheets

Quotes via IMPORTXML

Google Sheets has proven to represent a much wider range of parsing options. And the first thing I ran into was the IMPORTXML function, which allowed me to take almost any line from a web page. And for use it was only necessary to learn how to write xpath request. With its help, you can collect the necessary quotes and other information without scripts and integration with the API.

My Report Table Template, Named SilverFir: Investment Report

After about six months of research in 2018, the final version of my template was born, which became the starting point for the adult accounting of diversified assets “in one window”.

Asset allocation in SilverFir: Investment Report

Unfortunately, when a lot of IMPORTXML was used in the Table, you had to wait too long to download all the results, and it was also found that it was impossible to use the data received through IMPORTXML on schedule in Google scripts (because they might just not have time to load by the time they were used).

Quotes via Google Apps Script

When it became clear that with a large number of rows and requested data, it became inconvenient to use the reporting table on IMPORTXML - I had to look further.

Google Apps Script comes to the rescue in FIRE movement

Since I always believed that the FIRE movement is the freedom to do what you like, and not an accounting search for cents and certainly not a copy-paste of quotes from different sites. The structure and layout of the reporting table turned out to be very successful (other people noted this), but the method of obtaining quotes had to be changed again.

On Google scripts, parsing, in my opinion, looks more concise than on VBA.

 function GetFinanzenNet(ISIN, id) { var url = 'https://www.finanzen.net/suchergebnis.asp?_search=' + ISIN; try { var html = UrlFetchApp.fetch(url).getContentText(); } catch (error) { Logger.log("GetFinanzenNet. Symbol = " + ISIN + ".\n  . URL: " + url + ". HTML:\n" + html); return "" } if (id == "Price") { //  - Quote/Price var searchstring = 'text-sm-right text-nowrap">'; var index = html.search(searchstring); if (index >= 0) { var pos = index + searchstring.length var rate = html.substring(pos, pos + 50); rate = rate.split('<')[0]; rate = rate.replace(/\,/g, "."); rate = rate.replace(/\%/g, ""); rate = +rate; Logger.log("GetFinanzenNet. ID = " + id + ".\nPrice = " + rate + ". URL: " + url); return rate } } if (id == "Name") { // var searchstring5 = '<title>'; var index5 = html.search(searchstring5); if (index5 >= 0) { var pos5 = index5 + searchstring5.length var rate5 = html.substring(pos5, pos5 + 100); try { rate5 = rate5.split(' | ')[1]; rate5 = rate5.split(' | ')[0]; var res = rate5.substring(0, 1); //  if (res == "(") { rate5 = html.substring(pos5, pos5 + 100); rate5 = rate5.split(' | ')[0]; rate5 = rate5.substring(0, 18); } } catch (error) { return ""; } Logger.log("GetFinanzenNet.  Name  Symbol " + ISIN + ".\nName = " + rate5 + ". URL: " + url); return rate5 } } Logger.log("GetFinanzenNet. Symbol = " + ISIN + ".\n  . URL: " + url); return "" }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Already then it became clear that you can greatly simplify your entire accounting by indicating only the initial amount, quantity and date of purchase of the asset, and download all other parameters (including the name) from other (different) sites.

Ecosystem

But, when I began to deal with Google scripts, it was found that their application can be much wider than just parsing the data of interest - the components practically create their own service, where one Table manages updates, alerts, charts, presentations and can even be integrated with external resources.

The list for almost two years has turned out to be large and, most importantly, everything can be successfully automated on Google scripts based on only the initial price, date of purchase and asset identifier.

An asset in this case can be anything: a bond, a stock, physical real estate, coins, deposits - there is no limiter and there is a created template that can be customized for any specific request.

Total

Returning once again to the practical aspects of the FIRE movement early retirement pension in Russia, accounting has the most direct relation to early retirement, and it is correctly set up accounting that allows you to keep abreast, while at the same time not slipping into frequent viewing of quotes and current prices.

Posted by: Mikhail Shardin ,

November 25, 2019