Last year the number of private investors at Moscow Stock Exchange (MOEX) has doubled and reached 3.86 million: about 1.9 million people have opened accounts at MOEX in 2019. The Saint Petersburg Stock Exchange which specializes in trading of foreign company shares has seen its accounts increase three times from 910,000 to 3,06 million over the past year.



This means that almost 2 million newbies without any actual trading experience and lacking any specialized software for trading/position analysis have entered the market.

While the broker’s mobile applications designed for trading are continuously updated and, in general, quite user-friendly, the position analysis, particularly when several brokers are used, could raise issues. Google Sheets using free MOEX API may become a free solution equipped with automatically downloaded asset prices and parameters.

MOEX API in Google Spreadsheets

There are two options to be used with MOEX API:

• Formulas
• Google Apps Script

However, it appears that scripts used in free documents usually confuse people, especially if these people have some investments. People are even more confused if they do not understand the script language. That’s why I would like to focus on formulas only.

General description and explanation of all the requests that you can send to MOEX API is provided in the guidebook. However, I, for one, did not find this guidebook that easy to understand, so I had to spend quite a lot of time to cope with it. For that reason, I made a simple Google Sheet with examples I use myself.

Trading Mode Identifier

In MOEX API a lot of things depend on Trading Mode Identifier parameter (primary_boardID). You can look up this parameter at their website using the search function.


Trading Mode ID

You can also find this identifier using HTTP request to API:

https://iss.moex.com/iss/securities.xml?q=WRITE THE FULL INSTUMENT NAME OR ITS PART HERE


Trading Mode ID

Retrieve stock / bonds names at MOEX

It is really convenient that you can get the full security name. You can also get the short security name automatically.


Google Spreadsheets

Retrieve stock / bonds prices at MOEX

You can use numerous websites to receive prices for Russian assets, but when it comes to MOEX API relevant examples are introduced on this tab.

Google Spreadsheets

Retrieve next coupon payment at MOEX

You can receive coupon date and its value (either corporate or Federal Loan Bonds (OFZ) and Eurobonds) automatically.

Google Spreadsheets

Retrieve yield at MOEX

Unfortunately, you can obtain the dividend yield as of a specific date represented in a single formula only for OFZ, as they are very shortlisted. For corporate bonds, you’ll have to pick them out in the script or choose the initial search position manually.

Google Spreadsheets

Retrieve offer dates at MOEX

Planning your own funds becomes much easier, when you receive offer dates automatically.

Google Spreadsheets

Conclusion

MOEX API gives you powerful capabilities. In this article I described only those tools that I use as a long-term private investor.

Please note that I have no relations with Moscow Exchange and I use MOEX API only for personal needs.

Author: Mikhail Shardin,
February 6, 2020