Previously, I wrote about receiving data from the Moscow Exchange through the formulas of Google Sheets . However, the question remained: is it possible to obtain the same data when using Microsoft Excel or its free analogue LibreOffice Calc locally? Without using scripts or manual copying.
Microsoft Excel with formulas for obtaining data from MosbirzhaAnd you can give a positive answer to this question. This is even more convenient, since you do not have to expect the results of the IMPORTXML function to load in Google Tables.An analog of this function in Excel and Calc is a bunch of formulas: WEBSERVICE + FILTERXML (FILTER.XML) .When working with Microsoft Excel, there are some nuances:- These features are available only in Excel 2013 and later versions for Windows.
- These features will not return results on a Mac.
LibreOffice Calc has fewer such restrictions:- Requires LibreOffice 4.2 and higher.
- There are no restrictions on the OS used. Works under:
Windows
Linux
Mac OS - The Excel .xlsx file opens and is operational, but visually formatting may be broken.
I assembled a workable example " API Microsoft Excel.xlsx" with the functions that are described in the program interface to the information and statistical server of the Moscow Exchange (ISS / ISS) . Plus, he added some functions that were found by readers of the first part of the article about Google tables.Tested this file in Microsoft Excel 2019 under Windows 10 and in LibreOffice Calc 6.4 under Linux Mint 19.3. Under Mac OS, I was not able to test.Moscow Exchange API in MS Excel formulas
In general, all the requests that can be sent to the Mosbirzi API are in the directory . But for me personally, this guide is still not very clear, although I contact the API quite often.Bid Mode Id
In the Moscow Exchange API, a lot depends on the parameter “Trading mode identifier” (primary_boardid), which can be viewed directly on their website through the search form .
Trading mode identifier for Tinkoff shares.Also, this identifier can be viewed through a regular HTTP request to the API :https://iss.moex.com/iss/securities.xml?q= &iss.meta=off&securities.columns=name,emitent_inn,isin,secid,primary_boardid
Search through the HTTP request to the Mosbirzi API for the word PermAutomatic receipt of the name of stocks, bonds and ETFsIt is very convenient that you can get the full or short name of the instrument. For bonds, the full name is especially clear.
File " API Microsoft Excel.xlsx" with examples of automatically obtaining a name for different asset classes. Works correctly in LibreOffice CalcAutomatic receipt of current prices
This tab provides relevant examples for obtaining stock prices, bonds and ETFs from the Moscow Exchange.The price of the previous day is taken through PREVADMITTEDQUOTE, and not LAST with a 15 minute delay, because for some low-liquid instruments through LAST there may simply not be prices.
File " API Microsoft Excel.xlsx" with examples of automatically obtaining a name for different asset classes. Works correctly in LibreOffice CalcAutomatic receipt of dividend payments for shares
A very convenient function of the Moscow Exchange, which allows you to get not only the value of the current payment, but also the history of dividend payments along with dates and values.
File " API Microsoft Excel.xlsx" with examples of automatically obtaining a name for different asset classes. Works correctly in LibreOffice CalcAutomatic receipt of bond payments
For bonds (not only corporate, but also OFZ and Eurobonds), you can automatically receive the date of payment of the next coupon and its value.
File " API Microsoft Excel.xlsx" with examples of automatically obtaining a name for different asset classes. Works correctly in LibreOffice CalcAutomatic receipt of offer dates
It is convenient to plan your own finances by receiving offer dates (the date on which the investor or issuer has the right to early repay the bond at par value) automatically.
File " API Microsoft Excel.xlsx" with examples of automatically obtaining a name for different asset classes. Works correctly in LibreOffice CalcTotal
The Moscow Exchange API provides broad capabilities that are much broader than those described in this article. This article is a kind of cheat sheet for a long-term private investor who keeps records in a local file on his own computer.I also want to note that I have nothing to do with the Moscow Exchange and I use ISS Mosbirzhy only for my personal interests.Posted by Mikhail Shardin ,April 21, 2020