אַרייַנפיר ביטקאָין קורס צו עקססעל דורך פּאָווער קווערי

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 שנעל פאַרקויף and option Transfers through a specific bank (or any other you need) and press the button זוכן: 

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=פאַרקויפן&country_code=RU&amount=¤cy=רייַבן&place_country=RU&  online_provider=SPECIFIC_BANK&find-offers=Search

Then 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 – מאַכט קווערי. If you have Excel 2016, then on the tab דאַטע (דאַטע) דריקן דעם קנעפּל פון די אינטערנעט (פֿון אינטערנעט). 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 קערעקשאַן (רעדאַגירן):

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 צאָלונג אופֿן and buying rate Price / BTC – so you can safely distinguish them both with קטרל and by right-clicking on them, select the command ויסמעקן אנדערע שפאלטן (נעם אַוועק אנדערע שפאלטן) – 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 צאָלונג אופֿן און אויף די קוויטל טראַנספאָרמאַציע קלייַבן אַ קאָלעקטיוו Format – lowercase (Transform — Format — Lower case):

Now filter by column צאָלונג אופֿן using option Text Filters – Contains (Text filters — Contains):

In the filter window, immediately switch from above to the mode אַדישנאַלי (אַוואַנסירטע) 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 (OR) אַנשטאָט И (און) 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 צאָלונג אופֿן right click on column header ויסמעקן זייַל (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 ריפּלייסינג וואַלועס (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 ענגליש (יו. עס.) and data type – Дדעצימאַל נומער:

נאָך דריקט אויף 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 היים - נאָענט און לאָדן - נאָענט און לאָדן אין ... (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 דערהייַנטיקן & היט (דערפרישן), 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.

• אַרייַנפיר וועקסל רייץ פון די אינטערנעט
  
• Function to get the exchange rate for any given date
• אַסעמבאַל טישן פון פאַרשידענע טעקעס ניצן Power Query