Import bitcoin rate to Excel via Power Query

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 Fast sale and option Transfers through a specific bank (or any other you need) and press the button Search: 

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=sell&country_code=RU&amount=¤cy=RUB&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 – Power Query. If you have Excel 2016, then on the tab Data (Date) press the button From the Internet (From Internet). 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 Correction (Edit):

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 Payment method and buying rate Price / BTC – so you can safely distinguish them both with Ctrl and by right-clicking on them, select the command Delete other columns (Remove other columns) – 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 Payment method and on the tab Transformation choose a team Format – lowercase (Transform — Format — Lower case):

Now filter by column Payment method using option Text Filters – Contains (Text filters — Contains):

In the filter window, immediately switch from above to the mode Additionally (Advanced) 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) instead И (And) 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 Payment method right click on column header Delete column (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 Replacing values (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 English (US) and data type – Дdecimal number:

After clicking on 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 and Load — Close and Load in… (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 Update & Save (Refresh), 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.

• Import exchange rates from the Internet
  
• Function to get the exchange rate for any given date
• Assembling tables from different files using Power Query