змест
Выкажам здагадку, што вы праявілі дзелавое чуццё і інтуіцыю і купілі ў мінулым некалькі порцый якой-небудзь криптовалюты (таго ж биткойна, напрыклад). У выглядзе разумнай табліцы ваш «інвестыцыйны партфель» выглядае так:
Задача: хутка ацаніць бягучы кошт вашых укладанняў па бягучым курсе криптовалюты. Возьмем курс у інтэрнэце з любога падыходнага сайта (біржы, абменніка) і сярэдні па надзейнасці.
Адно з рашэнняў - класічны вэб-запыт - я ўжо падрабязна разглядаў на прыкладзе імпарту валютнага курсу. Зараз давайце паспрабуем, для разнастайнасці, выкарыстаць іншы метад - надбудову Power Query, якая ідэальна падыходзіць для імпарту дадзеных у Excel з знешняга свету, у тым ліку з Інтэрнэту.
Выбар сайта для імпарту
З якога сайта мы будзем браць дадзеныя – гэта, па вялікім рахунку, не мае значэння. Класічны вэб-запыт Excel вельмі патрабавальны да структуры і ўнутранага дызайну імпартаванай вэб-старонкі і часам працуе не на ўсіх сайтах. Power Query значна больш усяедны ў гэтым пытанні. Такім чынам, вы можаце ўзяць сярэдні курс пакупкі на выбар:
- у абменніках www.bestchange.ru – вялікі выбар варыянтаў, мінімальныя рызыкі, але не вельмі выгадны курс абмену
- з гандлёвай платформы www.localbitcoins.net - крыху больш рызыкі, але значна лепшы курс
- з сайта біржы - калі вы гандлюеце непасрэдна на біржы, то гэты артыкул вам наўрад ці спатрэбіцца 🙂
Спачатку адкрыем патрэбны нам сайт у браўзэры. Возьмем для канкрэтнасці гандлёвую платформу localbitcoins.net. Выберыце верхнюю ўкладку Хуткі продаж і варыянт Пераклады праз пэўны банк (або любы іншы, які вам патрэбны) і націсніце кнопку пошук:
Цяпер трэба скапіяваць адрас якая з'явілася старонкі ў буфер абмену, т.к. ён змяшчае ўсе неабходныя параметры запыту:
https://localbitcoins.net/instant-bitcoins/?action=прадаваць&код_краіны=RU&сума=¤cy=руб&place_country=RU& online_provider=КАНКРЭТНЫ_БАНК&find-offers=ПошукЗатым справа за Power Query.
Імпарт курса ў Excel з дапамогай Power Query
Калі ў вас усталяваны Excel 2010-2013 і Power Query як асобная надбудова, то патрэбная каманда знаходзіцца на аднайменнай ўкладцы – Запыт на харчаванне. Калі ў вас Excel 2016, то на ўкладцы Дата (Дата) націсніце кнопку З Інтэрнэту (З Інтэрнэту). У якое з'явілася акне вам трэба ўставіць скапіяваны адрас вэб-старонкі з папярэдняга абзаца і націснуць OK:
Пасля разбору вэб-старонкі Power Query адлюструе акно са спісам табліц, якія можна імпартаваць. Неабходную табліцу трэба знайсці ў спісе злева (іх некалькі), арыентуючыся на прэв'ю справа, а затым націснуць кнопку ніжэй Карэкцыя (Рэдагаваць):
Пасля гэтага адкрыецца галоўнае акно рэдактара запытаў Power Query, у якім мы можам выбраць толькі патрэбныя радкі і усреднить па іх стаўку пакупкі:
Рэкамендую неадкладна перайменаваць наш запыт на панэлі справа, даўшы яму нейкую разумную назву:
Фільтраванне і ачыстка дадзеных
У далейшым нам спатрэбяцца толькі слупкі з апісаннямі Спосаб аплаты і курс пакупкі Кошт / BTC – так што вы можаце смела адрозніваць іх абодвух Ctrl і пстрыкнуўшы па іх правай кнопкай мышы, абярыце каманду Выдаліць іншыя слупкі (Выдаліць іншыя слупкі) – будуць выдалены ўсе слупкі, акрамя выбраных.
Дапусцім, мы хочам адабраць толькі тых трэйдараў, якія працуюць праз Ашчадбанк. Фільтр - рэч звыклая, але нюанс у тым, што фільтр у Power Query адчувальны да рэгістра, то ёсць Ашчадбанк, Ашчадбанк і Ашчадбанк для яго не адно і тое ж. Таму, перш чым выбіраць патрэбныя радкі, прывядзем усе апісанні да адной формы. Для гэтага трэба выбраць слупок Спосаб аплаты і на ўкладцы Пераўтварэнне выбраць каманду Фармат – малыя літары (Пераўтварэнне — Фармат — Ніжні рэгістр):
Цяпер фільтруйце па слупку Спосаб аплаты выкарыстоўваючы варыянт Тэкставыя фільтры - змяшчае (Тэкставыя фільтры — Змяшчае):
У акне фільтра адразу пераключыцеся зверху ў рэжым Дадаткова (Advanced) і ўвядзіце тры правілы выбару:
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) замест И (І) У адваротным выпадку правіла не будзе працаваць карэктна. Пасля націску на OK На экране павінны заставацца толькі патрэбныя нам параметры:
Цяпер зніміце калонку Спосаб аплаты пстрыкніце правай кнопкай мышы на загалоўку слупка Выдаліць слупок (Выдаліць слупок) і далей працаваць з пакінутым адным слупком курсаў:
Праблема яго ў тым, што там, акрамя лічбы, ёсць яшчэ і пазначэнне валюты. Гэта можна лёгка ачысціць з дапамогай простай замены, пстрыкнуўшы правай кнопкай мышы на загалоўку слупка і выбраўшы каманду Замена значэнняў (Замяніць значэнні):
Лічбы, атрыманыя пасля выдалення RUB, па сутнасці, таксама яшчэ не з'яўляюцца лічбамі, таму што ў іх выкарыстоўваюцца нестандартныя падзельнікі. Гэта можна вылечыць, націснуўшы кнопку фарматавання ў загалоўку табліцы і выбраўшы опцыю Выкарыстанне Locale (Выкарыстоўваць мясцовыя):
Найбольш прыдатным месцам будзе Англійская (ЗША) і тып дадзеных - Ддзесятковы лік:
Пасля націску на кнопку OK мы атрымаем поўныя лікавыя значэнні ставак пакупкі:
Засталося вылічыць сярэдняе для іх па табл Трансфармацыя – Статыстыка – Сярэдняя (Пераўтварэнне — Статыстыка — Сярэдняе) і загрузіць атрыманы лік на ліст з камандай Галоўная — Зачыніць і загрузіць — Зачыніць і загрузіць у… (Дадому — Зачыніць і загрузіць — Зачыніць і загрузіць у...):
Цяпер мы можам дадаць спасылку на запампаваны курс у формулу ў табліцу нашага партфеля і разлічыць розніцу кошту ўсіх нашых інвестыцый на бягучы момант:
Цяпер вы можаце перыядычна адкрываць гэты файл, націскаць правай кнопкай мышы на падказку і выбіраць каманду Абнавіць і захаваць (Абнавіць), назірайце за зменамі, якія будуць аўтаматычна загружаны ў нашу табліцу.
PS
Як вы можаце сабе ўявіць, сапраўды такім жа чынам вы можаце імпартаваць курс не толькі биткойна, але і любой іншай валюты, акцыі або каштоўнай паперы. Галоўнае - знайсці прыдатны сайт і пабудаваць запыт, а далей разумны Power Query усё зробіць.
- Імпарт курсаў валют з Інтэрнэту
- Функцыя для атрымання абменнага курсу на любую дату
- Зборка табліц з розных файлаў з дапамогай Power Query