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.
![](https://habrastorage.org/webt/oz/xf/8j/ozxf8jea2oijos5s7gdc7hrrne0.gif)
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.
There are two options to be used with MOEX API:
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.
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.
![](https://habrastorage.org/r/w1560/webt/o2/ok/6n/o2ok6nv3g8w5v9pywsmj_ur1790.png)
Trading Mode ID
You can also find this identifier using HTTP request to API:
![](https://habrastorage.org/r/w1560/webt/-t/kf/s4/-tkfs4u53nwg2kz4ml10uczqbjo.png)
Trading Mode ID
It is really convenient that you can get the full security name. You can also get the short security name automatically.
![](https://habrastorage.org/r/w1560/webt/t4/fl/wk/t4flwkaeyn67kezftrnmy0kv76a.png)
Google Spreadsheets
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.
![](https://habrastorage.org/r/w1560/webt/tu/zb/nm/tuzbnmm3wljdy4iwokbzgkxxczk.png)
Google Spreadsheets
You can receive coupon date and its value (either corporate or Federal Loan Bonds (OFZ) and Eurobonds) automatically.
![](https://habrastorage.org/r/w1560/webt/9_/my/ne/9_myner3lffbrjr4122rxwvnpv8.png)
Google Spreadsheets
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.
![](https://habrastorage.org/r/w1560/webt/go/xx/ue/goxxuefjmn-dkpgsk_8fgrvqnjs.png)
Google Spreadsheets
Planning your own funds becomes much easier, when you receive offer dates automatically.
![](https://habrastorage.org/r/w1560/webt/i2/vk/nd/i2vknd_cwwhv7fthmyzwijtkiko.png)
Google Spreadsheets
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
![](https://habrastorage.org/webt/oz/xf/8j/ozxf8jea2oijos5s7gdc7hrrne0.gif)
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.
![](https://habrastorage.org/webt/o2/ok/6n/o2ok6nv3g8w5v9pywsmj_ur1790.png)
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
![](https://habrastorage.org/webt/-t/kf/s4/-tkfs4u53nwg2kz4ml10uczqbjo.png)
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.
![](https://habrastorage.org/webt/t4/fl/wk/t4flwkaeyn67kezftrnmy0kv76a.png)
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.
![](https://habrastorage.org/webt/tu/zb/nm/tuzbnmm3wljdy4iwokbzgkxxczk.png)
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.
![](https://habrastorage.org/webt/9_/my/ne/9_myner3lffbrjr4122rxwvnpv8.png)
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.
![](https://habrastorage.org/webt/go/xx/ue/goxxuefjmn-dkpgsk_8fgrvqnjs.png)
Google Spreadsheets
Retrieve offer dates at MOEX
Planning your own funds becomes much easier, when you receive offer dates automatically.
![](https://habrastorage.org/webt/i2/vk/nd/i2vknd_cwwhv7fthmyzwijtkiko.png)
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