Contents
Let’s assume that you showed business sense and intuition and bought several portions of some cryptocurrency (the same bitcoin, for example) in the past. In the form of a smart table, your “investment portfolio” looks like this:
Task: to quickly evaluate the current value of your investments at the current rate of cryptocurrency. We will take the course on the Internet from any suitable site (exchange, exchanger) and average for reliability.
One of the solutions – a classic web request – I have already considered in detail using the example of importing the exchange rate. Now let’s try, for a change, to use another method – the Power Query add-in, which is ideal for importing data into Excel from the outside world, including from the Internet.
Choosing a site to import
From which site we will take the data – this, by and large, does not matter. The classic Excel web query is very demanding on the structure and internal design of the imported web page and sometimes does not work on every site. Power Query is much more omnivorous in this matter. So you can take the average purchase rate to choose from:
- in exchangers www.bestchange.ru – a large selection of options, minimal risks, but not a very profitable exchange rate
- from the trading platform www.localbitcoins.net – a little more risk, but a much better rate
- from the exchange website – if you trade directly on the exchange, then you hardly need this article 🙂
First, let’s open the site we need in the browser. Let’s take, for concreteness, the trading platform localbitcoins.net. Select the top tab Firotana bilez and option Transfers through a specific bank (or any other you need) and press the button Gerr:
Now you need to copy the address of the page that appears to the clipboard, because. it contains all the request parameters we need:
https://localbitcoins.net/instant-bitcoins/?action=firotin&country_code=RU&amount=¤cy=DIHEVDAN&place_country=RU& online_provider=SPECIFIC_BANK&find-offers=SearchThen it’s up to Power Query.
Importing a course into Excel using Power Query
If you have Excel 2010-2013 and Power Query installed as a separate add-in, then the command we need is on the tab of the same name – Pirsa Hêzê. If you have Excel 2016, then on the tab Jimare (Rojek) bişkojk bike Ji Înternetê (Ji Înternetê). In the window that appears then you need to paste the copied web page address from the previous paragraph and click OK:
After parsing the web page, Power Query will display a window with a list of tables that can be imported. You need to find the required table in the list on the left (there are several of them), focusing on the preview on the right, and then click the button below Serrastkirî (Weşandin):
After that, the main window of the Power Query query editor will open, in which we can select only the necessary rows and average the purchase rate over them:
I recommend immediately renaming our request in the panel on the right, giving it some sane name:
Filtering and cleaning data
In the future, we will only need columns with descriptions rêbaza dayina and buying rate Price / BTC – so you can safely distinguish them both with Ctrl and by right-clicking on them, select the command Stûnên din jêbirin (Stûnên din rakin) – all columns will be deleted except for the selected ones.
Let’s say that we want to select only those traders who work through Sberbank. The filter is a familiar thing, but the nuance is that the filter in Power Query is case sensitive, i.e. Sberbank, Sberbank and Sberbank are not the same for him. Therefore, before selecting the necessary lines, let’s bring the case of all descriptions to one form. To do this, you need to select a column rêbaza dayina û li ser tabê veguherîna tîmek hilbijêre Format – lowercase (Transform — Format — Lower case):
Now filter by column rêbaza dayina using option Parzûnên Nivîsar - Dihewîne (Fîlterên nivîsê - Dihewîne):
In the filter window, immediately switch from above to the mode Additionally (Pêşveçû) and introduce three rules for selection:
As you might guess, by doing this we select all the lines where the word “sber” is present in or English, plus those who work through any bank. Don’t forget to set a logical link on the left Or (AN) di ber И (Û) Otherwise, the rule will not work correctly. After clicking on OK Only the options we need should remain on the screen:
Now remove the column rêbaza dayina right click on column header Stûna jêbirin (Remove column) and work further with the remaining single column of courses:
The problem with it is that there, in addition to the number, there is also a currency designation. This can be easily cleaned up with a simple substitution by right-clicking on the column heading and selecting the command Li şûna nirxên (Replace values):
The numbers obtained after the removal of RUB, in fact, are also not numbers yet, because they use non-standard delimiters. This can be cured by clicking the format button in the table header and then selecting the option Using Locale (Use locals):
The most appropriate locale would be Îngîlîzî (US) and data type – Дjimara dehanî:
Piştî hilweşînê OK we will get the full numerical values of the purchase rates:
It remains to calculate the average for them on the tab Transformation – Statistics – Average (Transform — Statistics — Average) and upload the resulting number to the sheet with the command Mal - Girtin û Barkirin - Girtin û Li… (Home — Close & Load — Close & Load To…):
Now we can add a link to the downloaded rate in the formula to our portfolio table and calculate the difference in value for all our investments at the current moment:
Now you can periodically open this file, right-click on the prompt and select the command Nûvekirin & Save (Hênikkirin), observe the changes that will be automatically loaded into our table.
PS
As you can easily imagine, in exactly the same way you can import the rate of not only bitcoin, but also any other currency, stock or security. The main thing is to find a suitable site and build a query, and then smart Power Query will do everything.
- Ji Înternetê rêjeyên danûstendinê derxînin
- Function to get the exchange rate for any given date
- Bi karanîna Power Query tabloyên ji pelên cihêreng berhev dikin