Free API Moscow Stock Exchange (MOEX) in Google Sheets

    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

    Similar posts

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 1

      0

      Feel free to ask questions.

      Only users with full accounts can post comments. Log in, please.