Easily enriching publicly available data (and really any other kind of data)
[Disclaimer: the Excel-JSON import offered by the Power Query Excel add-in is only available for Windows — upvote this UserVoice feature request so the team prioritizes adding the PowerQuery Excel add-in for Mac.]
As part of a UW class on information visualization taught by Brock Craft, I found myself changing gears from big data, to much smaller data.
Data wrangling is a necessary step when working with publicly available data and enriching/preparing said data for consumption by apps, services, or even visualizing it directly with libraries such as D3.js. Online services such as the Microsoft Azure Maps service API or the Google Maps API can help to enrich location data by either geocoding, or reverse-geocoding depending on the data available, and format needed. In my case, I had a very simple dataset that had (among many) a column for the name of a US city, and another for the US state for each row.
My goal: enrich each row in the dataset with the latitude and longitude to be able to use D3.js more efficiently.
Tools used
-
— super easy to create and format requests to web APIs. — my favorite lightweight editor — the ultimate application to work with smaller datasets by using pivot tables, look-ups, etc. — used to query each city and state in order to obtain the address, geographic coordinates, and other useful pieces of data, such as county, state initials, etc.
Getting the data from Azure Maps using their API
[As a prerequisite, you need an Azure subscription — you can get some free credit when signing up for Azure]
The first step was to create an Azure Maps account from inside the Azure Portal and obtain an API key. You can see how to do it in the Azure Maps documentation here.
With the Azure Maps API key, I used the Postman app to create a few sample requests and ensure the key was working nicely. You can see how to submit them in the How to Search For an Address section in the Azure Maps docs.
Once the basics worked, I made an Excel pivot table from the original dataset to list the distinct combinations of city and state and used the preview batch request API to send a query with them. This was a better approach than querying for the same city and state hundreds, or thousands of times.
I copied the response data into VS Code and saved it as .JSON to then work with the file in Excel.
Wrangling the data in Excel (Windows only)
Open the JSON file using the Power Query add-in:
Data →Get Data →From File →From JSON
Once you select the file, you’ll see the root nodes in the JSON file. Note that these are links that you can navigate to.
The first step in the Power Query Editor is to convert the nodes into columns.
Record Tools →Convert →Into Table
They will look like this once they are converted:
Click on the item List since it contains all the records we’re interested in. The summary shows the summary of the API request; for this process it is not relevant.
We’ll do the same transforming into table at this level:
After it is converted to a table, you’ll see a small icon that lets you expand the contents into columns. Click it to see the contents of the record object and to expand it:
After expanding, the table will show a record object and a list object in each row. This is because each row represents:
- A query record (the query we did for each row, such as Paris, Texas, US, or Seattle, Washington, US).
- A list of the associated search results for the query which can contain 1 or more results depending on the request settings. In this case, to make my life easier, in the request I specified only one.
If you notice the Postman screenshot, shows the first few rows of the result — you can see how results is in fact a list of records.
With this table, we will do the exact same thing again for each objects column→ Expand to new rows.
After expanding the query summaries, we scroll right, and then expand the results objects column:
This expansion will change the object from list to record because each list has only one record:
We expand this again:
After expanding, we’ll see data, plus some more objects:
From this table, we will expand the address objects once more to get the actual addresses, and the position object to get the latitude and longitude too:
At this point, we’ll see the actual latitude and longitude. Whew!
Load the data into the Excel worksheet
Once we’ve expanded the necessary objects (and if you want, hid unnecessary columns), we can save and load the table into Excel:
Once you load the data into the Excel sheet, it will be ready to use:
From this point on you can filter the data, create a pivot table, or do any other thing that you need to refine the dataset that you’re working with.
Conclusion
This guide covered some basic steps to import a JSON file into excel and how to expand the contents to see the actual data returned by the Azure Maps API.
I loved using the API directly — it took me in total about 20 minutes to geocode about
4,000 distinct addresses, which I then added to my main dataset.
The steps shown here worked well for my needs, but be aware that there are other ways to do this using Python, or even using other services, such as Google Maps. Depending on your specific needs you might need to fine tune the API request, or filter specific fields returned by the API.
With this wrangling step complete, you should be able to import your data into D3.js or other application and start to work on the visualization.
Parsing JSON in Excel VBA
I have the same issue as in Excel VBA: Parsed JSON Object Loop but cannot find any solution. My JSON has nested objects so suggested solution like VBJSON and vba-json do not work for me. I also fixed one of them to work properly but the result was a call stack overflow because of to many recursion of the doProcess function.
The best solution appears to be the jsonDecode function seen in the original post. It is very fast and highly effective; my object structure is all there in a generic VBA Object of type JScriptTypeInfo.
The issue at this point is that I cannot determine what will be the structure of the objects, therefore, I do not know beforehand the keys that will reside in each generic objects. I need to loop through the generic VBA Object to acquire the keys/properties.
If my parsing javascript function could trigger a VBA function or sub, that would be excellent.
11 Answers 11
If you want to build on top of ScriptControl , you can add a few helper method to get at the required information. The JScriptTypeInfo object is a bit unfortunate: it contains all the relevant information (as you can see in the Watch window) but it seems impossible to get at it with VBA. However, the Javascript engine can help us:
- If the JScriptTypeInfo instance refers to a Javascript object, For Each . Next won’t work. However, it does work if it refers to a Javascript array (see GetKeys function).
- The access properties whose name is only known at run-time, use the functions GetProperty and GetObjectProperty .
- The Javascript array provides the properties length , 0 , Item 0 , 1 , Item 1 etc. With the VBA dot notation ( jsonObject.property ), only the length property is accessible and only if you declare a variable called length with all lowercase letters. Otherwise the case doesn’t match and it won’t find it. The other properties are not valid in VBA. So better use the GetProperty function.
- The code uses early binding. So you have to add a reference to «Microsoft Script Control 1.0».
- You have to call InitScriptEngine once before using the other functions to do some basic initialization.
UPDATE 3 (Sep 24 ’17)
Check VBA-JSON-parser on GitHub for the latest version and examples. Import JSON.bas module into the VBA project for JSON processing.
UPDATE 2 (Oct 1 ’16)
However if you do want to parse JSON on 64-bit Office with ScriptControl , then this answer may help you to get ScriptControl to work on 64-bit.
UPDATE (Oct 26 ’15)
Note that a ScriptControl -based approachs makes the system vulnerable in some cases, since they allows a direct access to the drives (and other stuff) for the malicious JS code via ActiveX’s. Let’s suppose you are parsing web server response JSON, like JsonString = «
Trying to avoid that, I’ve created JSON parser based on RegEx’s. Objects <> are represented by dictionaries, that makes possible to use dictionary’s properties and methods: .Count , .Exists() , .Item() , .Items , .Keys . Arrays [] are the conventional zero-based VB arrays, so UBound() shows the number of elements. Here is the code with some usage examples:
One more opportunity of this JSON RegEx parser is that it works on 64-bit Office, where ScriptControl isn’t available.
INITIAL (May 27 ’15)
Here is one more method to parse JSON in VBA, based on ScriptControl ActiveX, without external libraries:
How to Import JSON to Excel Using VBA
Howdee! It’s becoming increasingly more common for data to be generated in a JSON format as opposed to XML. XML was widely used for years, but recently JSON has started to become the preferred method of data exchange. Many REST APIs have the option to return both but the majority that I interact with default to returning JSON formatted data. Therefore, as excel ninjas, it’s important to understand how to import JSON to Excel for analysis. Before we begin on that route, let’s take a quick moment to talk about what JSON is for those that aren’t familiar with it.
Curious how to do this in VSTO? Click Here!
What is JSON?
JSON stands for JavaScript Object Notation and is a lightweight data-interchange format. In layman’s terms, it is a string of text that represents a universal data structure. It is easy for humans to read (when formatted properly) and, because of the universal structure, it is very easy and fast for machines to parse and generate. It’s made simple because JSON, at it’s most basic, is only two data types. It is made up of objects, or arrays, or a combination of both. Objects are made up of key/value pairs (often called a dictionary) and arrays are simply collections of values or objects separated by a comma.
It’s important to note that object/dictionary and array are the terminology applicable to the .NET language. Other languages might have other terminology such as hash table for object, or vector for an array. This site gives a great high level, cross-language overview of JSON.
JSON Object
A JSON object always begins with < and ends with >. The key is separated from its value with a colon (“:”). The key/value pairs will then be separated by commas all within the curly brackets. In the image below, the first key “color” corresponds to its value “red”, while the second key “value” corresponds to the red hex code “#f00”. Hopefully you can see why this might be called a dictionary as you look up the key (word to define) to get its value (definition of the word).
JSON Array
A JSON array, in its simplest form, is a list of values separated by a comma that begins with [ and ends with ]. In the below image, the JSON array is a list of string data points. We can see that they are colors but there is no key in this example explicitly telling that this string represents a color. Therefore, JSON is most commonly seen as a combination of arrays and objects.
JSON Objects & Arrays Together
Most of the time, JSON is returned as an array of objects. If you recall, an object always begins and ends with curly brackets. Therefore, the array of objects would have several objects enclosed in curly brackets that are separated by commas and all objects are enclosed in normal brackets (the array). That looks like this image:
In this example, each object represents a color and its corresponding hex code. In Excel, this would likely be two columns of data. One column for color, and the other for its hex code value. Now that you’re more familiar with JSON data structure, let’s learn how to import JSON to Excel!
Setup JSON to Excel
There are several libraries out there to help you import JSON to Excel. The most popular of these is VBA-JSON which is available here on GitHub. This library will do most of the hard work of parsing the JSON string to a dictionary for you and then you can write the values to an excel worksheet. When you download the latest release, you’ll need to import the “JsonConverter.bas” file to your VBA project. This GIF shows how that is accomplished. You will also need to add a reference to the “Microsoft Scripting Runtime” by clicking tools from the ribbon, selecting references and checking the box for Microsoft Scripting Runtime as shown in the screen grab.
Once you have completed both steps, insert a new module into your project and title it whatever you like. To get started, we need to dimension some variables to use later.
The ws variable will represent a worksheet in excel. In this example, we will use this worksheet to both read in our JSON string from and write the parsed JSON to Excel. To assign a worksheet to this variable, set it like this:
The jsonText variable will represent our string of valid JSON data. For this example, I’ve pasted that string of colors and hex codes we looked at earlier into cell A1 on the JSON to Excel Example tab. To assign that string to a variable, type this code:
This step is not necessary for the code to work for us. We could simply reference the cell in the worksheet that contains the JSON. However, most of the time you’ll be returning your JSON string from another data source, most likely a HTTP GET Web Call. If you’re not familiar with HTTP Requests in VBA, click here to learn more.
Lastly, to put the JSON string into the jsonObject, we will use one of the methods contained in the JsonConverter file you imported to begin this example. Since the builder of this library made all of the subroutines public, it is callable from the module this code is in. That call looks like this:
This call instructs the JsonConverter module to parse the JSON string you’ve passed it using the jsonText variable to a collection of dictionaries that we can now access. Now that we’ve finished our setup, we can start learning to import JSON to Excel in the next section.
Import JSON to Excel
The code to import JSON to Excel might sound daunting, but it is much easier than it sounds. At a high level, all you have to do is loop through the jsonObject you’ve created, and write each dictionary value to the sheet. The wonderful thing about dictionaries, is this is easy to do with a for each loop. I’ve added a couple of variables and a for each loop to our code and the result is this:
I set a counter variable, i, that I can use to tell the loop which row to write the data to. Next, create my column headers for the data. Then, for each “item” in the jsonObject write that dictionaries values to the cell row I indicate and increment my counter variable. That’s it! The results of our import JSON to Excel code looks like this:
As you can see, we’ve turned that odd-looking string of text in cell A1 to an easy to understand table of data with just a few lines of code!
Understanding JSON to Excel (Advanced)
Now, I do want to point out that understanding the structure of the JSON you’ll be parsing is very important. Within the for each loop, the code can access the value of each object by referencing its key. This goes back to understanding the key/value pair relationship of JSON and the dictionary structure to which it is parsed. The reason I stress this so much is because, when you’re pulling back more complex looking JSON, you may need to have multiple levels of dictionary references to access the value you want. There may also be times when you have to nest for loops when you have an array of objects with arrays of objects nested inside each object.
For example, let’s look at an example where the JSON is structured as a single object with key “data” and the value is an array of data about employee’s and their organizations:
As you can see, we have an object with an array nested inside. Some objects in the array contains other nested objects as well. This multi-tiered data structure is very common when pulling data from a REST service and was very confusing to me when I first began trying to import JSON to Excel. Let’s examine what the code looks like for this scenario before we dive any deeper.
The code overall is the exact same, just referencing a different tab and added more fields to pull out. The change I want to point out is to the for loop itself. The beginning of the for loop now starts by referencing the overall object that contains the array. This is referred to as the “root” of the JSON and you’ll see it if you’re querying a REST API for JSON data. This moves the for each loop inside the “data” level of the JSON and will now loop through all objects in the highest-level array. Had I left this out, the loop would only occur once as the JSON is all contained within a single object at its top level.
The second piece I want to point out is the lines for address and company. These are both objects contained within the parent object. So, in order for me to reach to value I want, I have to reference both keys to get the specific value I need. Lastly, I want to call out that you do not need to write every value from the returned JSON to the sheet. I have skipped over several fields to get the end result I desired and it does not affect my code results, which coincidently look like this:
I hope this brief explanation of how to import JSON to Excel was useful for you! Please let me know if it was helpful or if you have any questions in the comments below. Also, if you want to download the file with the example JSON and VBA code, you can do so here if you’re a free subscriber to the site. It’s free, easy, and only takes a moment to sign up.
Как найти и распарсить JSON на странице сайта в интернете с помощью VBA Excel?
В этом посте я покажу, как с помощью VBA сделать, то, для чего VBA вроде бы как изначально не предназначен – как получить значения нужных переменных из структуры JSON.
Этот пост сделан по мотивам просьбы моего первого и пока единственного подписчика, сделавшего комментарий на предыдущий пост «Макрос получения курсов доллара за период с сайта Банка России»
В чем отличие между сервисом ЦБ России и сайтом worldometers.info ? В том, что ЦБ предлагает XML сервис для автоматической загрузки информации (см. http://www.cbr.ru/development/SXML/ ) – ее неудобно смотреть через веб браузер, но удобно получать с помощью паучьих алгоритмов, а worldometers.info предлагает информацию для людей, а не для пауков.
Поэтому создаваемому на VBA паучку придется постараться, чтобы понять разметку «для людей».
Для работы паука необходимо дополнительно подключить три библиотеки:
1. Microsoft XML parser (MSXML) – тот же, что использовался для получения курсов ЦБ с сайта Банка России.
2. Библиотеку для работы с объектной моделью HTML.
3. Библиотеку для использования возможностей JavaScript из VBA.
Получаем html с сайта:
В полученном html паучку нужно найти и распарсить данные о количестве зарегистрированных случаев из формата JSON. Эти данные представлены вторым аргументом в вызове функции Highcharts.chart(chartName, chartData), которая на сайте рисует график.
В результате выполнения нижепредставленного кода в переменной strJson должна оказаться структура с данными в JSON формате.
Теперь самое интересное – как распарсить эту JSON структуру? Чистый VBA это делать не умеет. Но с JSON прекрасно работает JavaScript. А в VBA есть инструмент для использования возможностей JavaScript для пользователей MS Excel.
Мы можем в VBA получить уже распарсенную JSON переменную:
Проблема в том, что с объектом objJSON ничего нельзя сделать в рамках VBA – у него нет ни свойств, ни методов. Поэтому создаем эти методы на языке JavaScript. Нам нужно вытащить даты (xAxis) и количество (series->data):
Вот что пишем в VBA редакторе:
Загоняем данные в привычные VBA массивы:
Ну и раскатываем эти массивы по рабочему листу:
Вот, что получилось в результате на листе рабочей книги:
По этим данным легко построить график, например, такой:
Если захотите получить готовый файл Excel с рабочим кодом – дайте знать в комментариях.
635 постов 14.5K подписчиков
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.
Вообще этим обьектом (скрипт контролом) можно делать огромное количество операций и на vbscript и на javascript. Очень мощный инструмент для языков, не имеющих других механизмов работы с системой и ее библиотеками.
Решение конечно красивое, но смущает eval. Фактически он интерпретирует и исполняет код, которые ему передают строкой. Так что если Вы не уверены на 146% в надёжности источника данных, передавать полученный оттуда текст в eval ОЧЕНЬ плохая идея. Если источник взломает злоумышленник и передаст в ответ на ваш запрос текст с функцией, которая загружает шифровальщика, последствия могут быть печальными.
Если уж совсем нет другого выхода — хотя бы попытаться replace’ом, скажем, заменить все круглые скобки — хотя это не даст полной защиты, но хоть что-то
о Спасибо! оставлю тут заявку на файл эксель
Жоброго времени суток, первая мвсль, которая пришла после прочтения поста — это шпиЁнство за ценами конкурентов, реализуемо?
Парсинг персональных ачивок
Долгое время был в read-only, но на днях увидел пост о борьбе с баянами «своими» постами, и как бы сказать вдохновился. Ладушки, поехали.
На Пикабу, на странице ленты наград есть проблема: нельзя просмотреть персональные ачивки. Есть только разделы «все», «индивидуальные» (которая по сути «все») и «сообщества». Мда, и сортировки нет, одна кнопка «загрузить еще» красуется. А мне было бы интересно почитать посты, за которые пользователей наградили персональными ачивками. Штош, если гора не идет к Магомеду, решаем проблему сами.
За язык программирования взял Javascript, т.к. долгое время работаю на нем и хотелось запускать скрипт сразу из браузера. Заодно почему бы и не найти все ачивки, а еще сгруппировать по пользователям (это не все ачивки :(, см. п. 4 в проблемах в конце поста). Посты писать не умею, поэтому сразу результат (код ниже).
На треугольники-стрелочки (которые слева) можно нажать, чтобы раскрыть список.
Отлично! Теперь можно скопировать ник пользователя, вставить в URL после «собачки» (https://pikabu.ru/@) и почитать посты.
Ну и сам скрипт с коментариями, если кто-то тоже хочет запустить у себя. Инструкция по запуску и проблемы ниже.
UPD. Не знал, что Пикабу не переваривает код. Ссылка на исходник https://pastebin.com/C7eZp3H7
Инструкция по запуску скрипта (только десктоп):
Открываем любую страницу Пикабу в браузере, лишь бы домен был pikabu.ru, иначе возможна ошибка CORS.
Нажимаем F12 (или Ctrl + Shift + i) (или тыкаем правой кнопкой мыши в любое место и выбираем пункт Inspect/Посмотреть код) (или через настройки браузера справа вверху в пункте Developer Tools/Инструменты разработчика). Открылись инструменты разработчика.
Открываем вкладку Console/Консоль.
Вставляем весь скрипт и нажимаем Enter.
Ждем секунд 40 (зависит от интернет соединения, к серверу же обращаемся). Можно посмотреть ход выполнения скрипта во вкладке Network/Сеть. Там должны запросы к серверу отличающиеся только параметром page.
Смотрим в консоль, и если ошибки нет, любуемся результатом.
Проблемы, с которыми столкнулся:
Только методом тыка удалось составить необходимый header для AJAX запроса. И почему-то это ‘X-Requested-With’. Странно, почему не ‘Content-Type’?
Кнопка «загрузить еще» грузит гребаный HTML код. Да, для браузера пользователя так быстрее, чем получать JSON и по нему рендерить HTML, но твою мать 🙁
Я с этим не сталкивался, но если кто-то хочет запустить скрипт у себя, возможны синтаксические ошибки, т.к. использовал такие конструкции как spread, window.fetch и async/await. Эта проблема решается свежей версией браузера, в которой должны быть реализованы эти фичи.
Почему-то на сервере всего 36 страниц наград. Т.е. это не все награды. Может и можно как-то загрузить все, но не нашел.
Изначальная цель поиска персональных ачивок достигнута наполовину, потому что непонятно как понять, что ачивка персональная, а не выдана только одному пользователю. Поэтому просто взял весь список ачивок и отфильтровал по выданному кол-ву пользователей (чтобы был 1 пользователь-владелец), ну хоть как-то.
Странно, в предпросмотре поста, слетели табы/пробелы кода.
Ну вот и все. Сам скрипт занял примерно 2 часа, зато теперь можно почитать специфичные посты (жаль только что не все (см. п. 4 в проблемах)).
P.S. скрипт мой значит тег «мое» 🙂 Или нет, непонятно
P.P.S. чукча не писатель
Как я сделал курс по Экселю для новичков
Сделал я курс по Экселю для новичков случайно. Просто так получилось естественным путем.
Сдал небольшой проект одной компании, а в этом проекте по желанию заказчика был кусок на Excel-е.
В свободное время, думая, как бы комфортно развлечься, я просматривал наиболее интересные моменты из этой экселевской части проекта.
И вдруг мне подумалось. Если в основной части эклелевского файла убрать специфические моменты, макрос сделать простым и понятным, а форму сделать познавательно-развлекательной, да еще переделать документацию, то тогда. Получается почти готовый и вроде совсем неплохой «Курс по Экселю для новичков».
Подумал — сделал. Другого пути нет. Иначе, эта идея будет вертеться в голове, не давая покоя. Лучше идеи реализовывать, хотя бы для того, чтобы они отстали.
А тут еще сообразительная девчушка Светлана мимо пробегала. Я ее поймал и попросил пройтись по пошагово по этому курсу, с целью понять: реально ли научиться Экселю по моему новому курсу.
Ну, вот, пока она занималась, я фиксировал все ее телодвижения в видео формате.
В итоге получилось пять видео, вот их краткие описания.
Часть 1 — Подготовка. Запуск.
Что такое Excel и кому он нужен.
Где взять Excel, как установить. Лучшие альтернативы Excel.
Как запустить Excel. Где находится запускаемый файл Excel.
Часть 2 — Тренировка. Таблица умножения.
Разминочная тренировка. Выполняется на листе «Тренировка».
Всякие простейшие упражнения для разогрева.
Часть 3 — Задача «Распределение фонда премий».
Часть 4 — Турнирная таблица шахматный турнир.
Часть 5 — VBA (Visual Basic для приложений).
Использование VBA для расчета коэффициента Бергера и итоговых мест
Если есть у вас интерес быстро усвоить Excel (или альтернативную электронную таблицу), то добро пожаловать сюда:
Там все материалы в интуитивно понятной форме.
Уже 9
Самый большой проект на бесплатной cms
Итак, меня сделали админом этого сообщества 🙂 К сожалению, постов тут нет, так что модерировать особо нечего. Старые посты тоже модерировать уже нельзя 🙂
Попробую исправить ситуацию 🙂 Напишу пост сам и забаню кого-нибудь в комментариях 🙂
Насколько нелепо большие проекты вы писали на простых бесплатных cms? В моей практике, присутствует интернет магазин с собственным движком и практически годом разработки на wordpress и крупный проект в сфере телекома ( https://irt24.ru/ ).
Проект в сфере телекома, продажа интернет и ТВ тарифов, по сути сайт посредник(диллер) продающий тарифы Ростелекома/МТС и прочих через свой функционал.
На старте функционал выглядел не очень большим, на пару месяцев работы, подумаешь парочка тарифов и не такое делали. Максимум 6-8 месяцев, хотя сроки я не взялся сразу точно назвать.
На практике оказалось, что количество параметров редактируемых у этих тарифов физически в админке выглядит как простыня 1000px на 9000px, это сотни параметров различающихся в зависимости от группы тарифов и обладающие сложными взаимосвязями между собой.
Там где на сайте скромный каталог из четырех разделов, в админке скрыто ещё 10-к каталогов взаимосвязанных друг с другом. Тарифы телевидения содержат каналы, каналы распределяются по пакетам каналов, делятся по тематикам итд.
Стоит подметить, админка wordpress без особых проблем справилась с подобной загрузкой, каталоги весьма удобно распределились в меню, параметры редактирования тарифа органично распределись на всем доступном пространстве экрана администратора.
Один экран, с настройками роутеров(подобные экраны ещё два вверх, несколько вниз):
На самом сайте, разумеется фунционал тоже реализовывался без лишней скромности и с максимальным размахом. Беглый просмотр функционала Ростелекома и МТС по тематике показал крайнюю их узость и отсутствие массы ценных и необходимых пользователю функций.
Зато данные функции в избытке нашлись на американских площадках, откуда и начали щедро заимствоваться, попутно максимально расширяясь и усложняясь.
Так родились карточки генерируемые вплоть до запятой под каждый тариф и даже в мелочах редактируемые через админку, умная корзина позволяющая детально настроить все основные и дополнительные опции для тарифа, разумеется отдельные страницы для каждого тарифа позволяющие сделать тоже самое +дающие развернутое описание опций.
Тарифов оказалось довольно много и им явно нужен каталог для удобной сортировки и подбора нужного. С одной стороны, для этих целей могло хватить обычного каталога со списком тарифов, но это не создает достаточной боли в процессе разработки.
Поэтому был добавлен аяксовый каталог-фильтр поверх основного каталога, позволяющий тыкая параметры(вроде скорости интернета) тут же формировать список подходящих тарифов на странице. Разумеется, необходимо что бы в фильтре остались активны только те параметры которые встречаются среди уже отобранных тарифов.
То есть, если безлимитной мобильной связи среди тарифов со скоростью интернета 500 мб/с нет, то данная опция становится недоступной и мы показываем подсказку почему так произошло.
Впрочем, выше сказанное указывает только на фильтр. Где же каталог? Разумеется, наш фильтр должен уметь переключаться между категориями каталога, причем проверяя сможет ли он это сделать с уже выбранными опциями.
Обязательно, нужно добавить в фильтр возможность подбора по цене, это очень важно. Причем ползунков там должно быть два и оба в режиме реального времени оценивать своё состояние, нельзя прокрутить ползунок в состояние в котором фильтр не найдет тарифа.
Так же, никогда нельзя забывать про мобильные устройства, все должно работать на любом смартфоне/планшете/умном холодильнике(кто знает как жизнь повернется?)
И так, тарифы подобраны, дальше нужно их сравнить. Здесь нужно реализовать функционал сравнения по каждой опции и от щедрой души разрешить сравнивать между собой даже тариф только с интернетом, против тарифа только с ТВ. Вдруг появятся желающие(ценители).
В целом, реализация подобных монстров в одиночку на WP, это довольно спорная идея. Хотя вполне возможно, структура кода тоже получается максимально простой.
Чистый php, общение с базой данных через базовый функционал WP.
Jquery и парочка простых библиотек.
Файлы в коде изолированы по модулям, модуль сравнение содержит все свои стили, скрипты и файлы php шаблонов. То есть, практически автономен, имеет свой css и свой js, не подгружаемые другими модулями. Заметных сложностей с поиском кода не было 🙂
У нас с JS есть общая тайна — мы оба не разбираемся в больших числах
P.S. JS не поддерживает целочисленные типы, все числа являются number, который по факту double, поэтому самый большой int который JS может сохранить без потери точности — 2^53 — 1
Никаких ошибок при попытке обработать слишком большое число не будет — оно просто сконвертируется в ближайшее представимое double (удачного дебага, если это ID в базе)
То же касается JSON — как формат, он не содержит точных требований как обрабатывать числа — всё number и зависит от имплементации. Какие-то языки и библиотеки различают int и double автомагически, какие-то всё интерпретируют как double, какие-то падают на конверсии.
Javascript
Помощь с макросом
Дамы и господа, добрый день.
Есть макрос который берет выделенные ячейки и сохраняет их в папку с файлом в виде картинки JPG. Мне надо чтоб сохранял заранее заданный диапазон. Сердцем чую, что ответ довольно прост, но мозгом пока не допер. Вся надежда на ваши светлые умы.
Ипотека или аренда + инвестиции!?
Калькулятор с очень подробным расчетом. Эмоции в сторону. Цифры покажут ответ.
Если Вы найдете ошибку в расчетах или у Вас есть еще идеи для улучшения калькулятора, то пишите об этом! Ссылка на Excel калькулятор в конце статьи.
Для расчета нам потребуется учесть все расходы/доходы, связанные с ипотекой и сравнить такие же цифры при аренде и инвестициях.
Начнем. Для примера выбираем 2х комнатную квартиру для семьи, которая хочет жить в Красноярске в районе Южный Берег.
Вот то, что нужно учесть для ипотеки:
• Текущая рыночная стоимость квартиры 6 850 000 р.
• Если мы настроены пессимистично и верим в то, что рынок недвижимости сейчас рухнет, то можно это учесть. Закладываем в расчет падение рыночной цены квартиры на 20%. Получается 5 480 000р.
• Фактическая цена покупки квартиры может отличаться от рыночной. Может мы поторговались, а может наоборот взяли ипотеку под низкий процент с завышением цены. В нашем примере цена без изменений.
• Первоначальный взнос будет минимально возможный 15%. 1 027 500р.
• Заемные средства по ипотеке 5 822 500р.
• Если есть возможность использовать материнский капитал на первоначальный взнос, то нужно его учесть. В нашем случае без материнского капитала.
• Процентная ставка по ипотеке 6,7% от Сбербанка на Домклик. Программа с господдержкой на новостройки 2022.
• Будем рассматривать самую длинную ипотеку на 30 лет.
• Ежемесячный платеж по ипотеке 37 571р.
• Жилая площадь квартиры 58,4 кв.м.
• Количество лет заемщика для расчета страховки. 34 года.
• Страховка жизни и здоровья на год получается 28 933р.
• Страховка квартиры на год получается 13 101р.
• Новостройки сразу не переходят в собственность. Нужно учесть количество месяцев до сдачи дома. В нашем случае 14 месяцев.
• После приемки квартиры от застройщика потребуется какое-то время на переезд и подготовку квартиры. В нашем случае 1 месяц.
• Квартиры на Южном Берегу продаются с готовой отделкой. Заезжай и живи. Значит если мы не требовательны, то на доделку квартиры нам придется потратить минимум. Заложим 5 000р.
• Нам потребуется мебель и техника в квартиру. Опять же если мы не требовательны к обустройству квартиры, то обставить 2к квартиру с нуля обойдется примерно 300 000р.
• Нам потребуется эксперт при приемке квартиры от застройщика. Заложим на услуги 25 000р.
• Нам потребуются дополнительные расходы на оформление сделки. Закладываем 15 000р.
• Нужно учесть стоимость ежемесячных коммунальных услуг. 3 947р.
• Налог на квартиру в год 4 504р.
• Нужно учесть прочие ежемесячные расходы, которые могут быть связаны с приобретаемой квартирой. У дома могут отсутствовать бесплатные парковки и нам придется арендовать ежемесячно паркинг. В нашем случае дополнительных расходов нет.
• Так же нужно учесть прочие ежегодные расходы, которые могут быть связаны с приобретаемой квартирой. Например, мы хотим дополнительно застраховать гражданскую ответственность перед соседями. В нашем случае дополнительные расходы закладывать не будем.
• Периодически нам нужно будет делать косметический ремонт в квартире. Будем делать 1 ремонт в 5 лет. Всего 6 косметических ремонтов за весь срок ипотеки.
• Необходимо учесть стоимость будущих косметических ремонтов. Для этого указываем цену косметического ремонта на текущий момент. В дальнейшем вычислим цену по инфляции. Текущая стоимость косметического ремонта для такой квартиры 233 600р.
• За покупку квартиры можно сделать налоговый вычет. Для учета налогового вычета нужно указать официальный доход. В нашем случае указываем МРОТ. 15 279р.
• Указываем налоговый вычет за покупку квартиры. На текущий момент это 260 000р.
• Указываем налоговый вычет за проценты по ипотеке. На текущий момент это 390 000р.
• В дальнейшем при продаже квартиры нужно будет отдать 3% риелтору.
Чтобы расчет был точным нужно учесть статистику изменения цен за предыдущие года. Официальная статистика за 20 лет 2003-2022гг (Все подтверждения и расчеты в Excel файле):
• Ежегодный среднегодовой процент роста цены на недвижимость 10,86%.
• Ежегодный среднегодовой процент роста цены на коммунальные услуги 6,49%.
• Ежегодный среднегодовой процент роста цены на косметический ремонт (равно инфляции) 8,48%.
• Со временем дом с квартирой стареет и теряет в своей стоимости. Процент амортизации стоимости недвижимости после покупки 10,23%.
Вот то, что нужно учесть для аренды:
• Стоимость аренды такой же квартиры в месяц 35 000р.
• При съеме квартиры в аренду требуется минимум вложений, но все же нужно предусмотреть 5 000р в начале срока на мелочи.
• При аренде квартиры нужно оплачивать счетчики ежемесячно. Закладываем 1 081р.
• При съеме квартиры в аренду будут частые переезды. Предположим, что нужно будет переезжать каждые 2 года. За выбранный срок ипотеки в 30 лет в аренде будет 15 переездов.
• При переезде нужно оплачивать комиссию риелтору. Закладываем расходы на комиссию 50%.
Так же учитываем статистику изменения цен за предыдущие года. Официальная статистика за 20 лет 2003-2022гг (Все подтверждения и расчеты в Excel файле):
• Ежегодный среднегодовой процент роста цены на аренду 6,09%.
• Ежегодный среднегодовой процент роста цены на счетчики 6,49%.
• Деньги сэкономленные при съеме квартиры в аренду будут инвестироваться на Московскую Биржу. Среднегодовой процент полной чистой доходности на Московской Бирже 10,8%.
Все исходные данные собраны. Теперь нужно приступить к расчетам. Наша задача понять какая стратегия принесет больше прибыли в конце срока.
Сразу спойлер. Итоговые результаты:
Детальный расчет в таблице:
PS: в таблице заложен еще скрытый функционал. Сейчас в Excel файле эти строки и столбцы скрыты, чтобы не отвлекали от основной темы, но Вы можете их все отобразить. Скрытые возможности:
• Расчет инвестиций в золото.
• Сравнение инвестиции в ипотеку для сдачи квартиры в аренду против инвестиции в Московскую Биржу.
Еще раз обращаюсь: если Вы найдете ошибку в расчетах или у Вас есть еще идеи для улучшения калькулятора, то обязательно пишите об этом!