Нужно было написать парсер для сбора информации об юридических лицах Республики Казахстан.

Парсер реализован на Python 3.6

Вкратце как работает программа:

1.Скачиваем с сайта stat.gov.kz 16 архивов по областям и городам республиканского значения

2.Распаковываем архивы, получаем 16 xls файлов

3.Открываем по очереди файлы и из каждого листа копируем данные в общий csv-файл

import requests

for i in 17, 18, 19, 20, 21, 23, 24, 25, 26, 27, 28, 29, 31, 32, 33, 34:

    link = 'http://stat.gov.kz/getImg?id=ESTAT0861'+ str(i)

    file = 'D:\\BIN\\' + str(i) + '.zip'

    f=open(file,'wb')

    ufr = requests.get(link, verify = False)

    f.write(ufr.content)

    f.close()

import zipfile

for i in 17, 18, 19, 20, 21, 23, 24, 25, 26, 27, 28, 29, 31, 32, 33, 34:

    zip_ref = zipfile.ZipFile('D:\\BIN\\' + str(i) + '.zip', 'r')

    zip_ref.extractall('D:\\BIN\\Excel')

    zip_ref.close()

import pandas as pd

import glob

import os

import xlrd

ROOTDIR = 'D:\\BIN\\Excel\\'

wb_pattern = os.path.join(ROOTDIR, '*.xls')

workbooks = glob.glob(wb_pattern)

all_data = pd.DataFrame()

for f in workbooks:

    #ef=pd.ExcelFile(f)

    #cnt_sh = len(ef.sheet_names)

    ef = xlrd.open_workbook(f)

    cnt_sh = len(ef.sheet_names())

    #print(cnt_sh)

    for cnt in list(range(0,(cnt_sh))):

        df = pd.read_excel(f, sheetname = cnt, index_col=None, skiprows=3, dtype=str)

        #print(df)

        all_data = all_data.append(df,ignore_index=True)

        #print(all_data)

        all_data.to_csv('D:\\BIN\\Excel\\BIN.csv', sep='|', quotechar='#', encoding = 'utf8')

Что такое MAT (Moving Annual Total)?

Moving Annual Total – скользящая годовая сумма - это метод оценки в Business Intelligence данных, которые имеют сезонные колебания.

Суть метода заключается в сложении продаж/оборота/выручки/активной базы и т.д. предыдущих 12 месяцев, включая текущий.

Например, в ритейле некая категория товаров может продаваться лучше в зимний сезон, в то время как другая категория продается лучше в летний период.

Для примера я взял вымышленные данные по продажам товара, который имеет ярко выраженный тренд роста продаж к концу каждого года.

В итоге, имея данные продаж, к примеру, за 5 последних лет (помесячно) иногда бывает сложно сказать растут ли продажи или снижаются на протяжении этого периода (рисунок 1).

Рисунок 1

Глядя на рисунок 1, сложно предсказать тренд. На первый взгляд тренд кажется растущим.

Давайте добавим к графику линию тренда и посмотрим на его поведение (рисунок 2).

Рисунок 2

“Да, теперь-то мы однозначно уверены, что тренд растущий”: - скажут новички.

“Нет, если использовать MAT, то на этих данных будет два тренда: один растущий, а другой снижающийся”: - возразят им опытные аналитики.

Давайте посчитаем MAT. Для января 2013 года — это будет сумма продаж, начиная с февраля 2012 до января 2013 включительно, для февраля 2013 – сумма продаж с марта 2012 до февраля 2013 включительно и т.д. (рисунок 3)

Рисунок 3

Теперь добавим к графику линию MAT, по вспомогательной оси и посмотрим что из этого выйдет. Немного поигравшись с началом вертикальных осей можно получить вот такой красивый график поведения продаж помесячно и по MAT (рисунок 4), на котором четко видно, что пик продаж по MAT приходился на декабрь 2015 года и в дальнейшем снижается.

Рисунок 4

С точки зрения практического применения MAT может быть использован для оценки тренда продаж или тренда активной базы клиентов, также на практике видел, как с помощью MAT строили прогноз выручки.

Что касается технической реализации расчета MAT, то мне приходилось рассчитывать MAT на: Excel, SQL Server, Oracle, MDX, DAX.

Надеюсь в следующий раз, когда вы будете анализировать свои данные, один из графиков вы обязательно отведете под MAT.

Спасибо за внимание!

И так первая статья цикла «Постулаты Excel» посвящена важности своевременного сохранения.

Я думаю, что сторонников своевременного сохранения будет 99,9%, поэтому как таковой линии аргументации не будет.

Приведу несколько жизненных случаев, когда мы хватаемся за голову, когда уже становится слишком поздно:

1. Отключили электричество;
2. Программа не отвечает;
3. Ctrl+Z уже не помогает.

Чтобы избежать неприятных моментов важно вовремя сохранить выполненную работу.

В зависимости от объёма данных, с которым вы работаете, подход к сохранению может быть диаметрально разным.

К примеру, я бы не рекомендовал включать функцию автосохранения, если вы работаете с большим объемом данных, так как процесс автосохранения сохраняет все открытые книги, в которые вносились изменения каждые N-минут.

А теперь представьте ситуации, когда автосохранение неудобно или даже опасно:

1. Вы сконцентрировались на важной формуле и в это время запускается процесс сохранения, как минимум вы теряете логическую последовательность своих действий из-за вмешательства внешнего фактора.
2. Вы отключили пересчет формул, чтобы закончить все расчеты, потом нажать F9 и идти пить чай или на обед, но автосохранение нарушает ваши планы, и вы вынуждены ждать окончания процесса просчета формул, чтобы не потерять свои данные.
3. Автосохранение сохраняет все открытые книги, следовательно, если объем данных большой, то возможно нарваться и на пресловутое «Программа не отвечает» и вы будете вынуждены завершать процесс через Диспетчер задач и, следовательно, терять свои данные или переделывать заново.

Но когда вы работает с малым или средним объемом данных функция автосохранения ваш хороший помощник.

Давайте рассмотрим, как эту функцию включить и настроить или отключить в зависимости от ваших нужд.

Если у вас Excel 2007 нажмите кнопку Office (круглая возле строки меню), если у вас Excel 2010 и выше нажмите кнопку Файл (возле строки меню) и далее выберите «Параметры», в появившемся диалоговом окне выберите пункт «Сохранение» (рисунок 1.)

По умолчанию автосохранение включено и настроено на каждые 10 минут.

Вы можете выбрать интервал от 1 минуты до 120 минут и нажать ОК.

Если же вы хотите отключить автосохранение – снимите галочку и нажмите ОК.

Рисунок 1. Автосохранение

Так как я обычно работаю с большим объемом данных, функцию автосохранения я отключаю и самостоятельно слежу за актуальностью данных и именно поэтому постулат называется «Сохраняйся перед и после каждого серьезного изменения».

Т.е. когда вы понимаете, что: «сейчас я буду делать кое-что очень важное и я, в случае чего-нибудь, хотел бы начать с этого места» - сохранитесь.

И когда вы закончили делать что-то важное – сохранитесь (те, кто любит играть в компьютерные игры меня поймут).

Также рекомендую сохраниться перед протягиванием формулы на большое количество ячеек вниз (двойной клик), так как это может быть серьезная нагрузка на процессор и результат может вас огорчить зависанием, а сохранившись вы сможете завершить процесс через «Диспетчер задач» и уже по-другому подойти к решению задачи.

Я закончил рекомендовать, теперь давайте поговорим о способах сохранения:

Есть два основных способа сохранения:

1. «Сохранить»
2. «Сохранить как»

«Сохранить» – сохранение текущего файла с текущим именем в текущем месте с текущим расширением.

«Сохранить как» – позволяет сохранить копию файла с выбором нового имени, места и расширения.

«Сохранить» для нового, ранее несохраненного файла работает как «Сохранить как»

Как сохранять:

Самый быстрый способ – горячие клавиши:

Ctrl+S – Сохранить;
F12 – Сохранить как.

Способ на любителя – панель быстрого доступа:

Иконка дискеты – «Сохранить»;
Иконка для «Сохранить как» – отсутствует.

Способ для тех, кто не знает о существовании первых двух:

Файл --> «Сохранить» или «Сохранить как».

Наверное, это все, что я хотел рассказать про сохранение. Надеюсь кому-то это будет полезно.

1. Сохраняйся перед и после каждого серьезного изменения;
2. Не объединяй ячейки;
3. Начинай таблицу с ячейки A1;
4. Не оставляй пустых ячеек в таблице;
5. Используй сочетания клавиш;
6. Используй имена;
7. Форматирование ячеек перенеси на второй план, первым делом – содержание;
8. Работай на двух мониторах;
9. Выучи, наконец, пользовательские форматы;
10. Используй мастер функций;
11. Используй окно контрольного значения;
12. Блокнот твой помощник;
13. Используй просмотр формул;
14. Мгновенное заполнение: магия или ловкость рук?
15. Выучи раз и навсегда типы ссылок;
16. «Найти» и «Заменить» – два брата-акробата и их сводный брат «Выделить» - три кита, на которых держится скорость обработки данных;
17. Используй строку состояния для быстрой оценки данных;
18. Используй «Сортировку» и «Фильтрацию» правильно;
19. Выноси итоги в отдельную таблицу;
20. Не переходи к VBA, пока окончательно не будешь уверен, что в Excel это нельзя сделать встроенными средствами.

Часто бывает, что когда работаешь с большим объемом данных, приходится выбирать удобную для себя организацию строк и столбцов, но после того как находишь агрегаты интересующих данных, нужно построить таблицу итогов, где зачастую приходится перестраиваться и менять местами строки и столбцы таблицы (транспонировать) для дальнейшей визуализации или просто отправки бизнес-пользователям.

Наверняка вы знаете, что в меню специальной вставки есть функция "транспонировать", которая располагает ячейки в столбце в одну строку, и наоборот, но минусы этой функции в том, что корректно она работает только со значениями.

Что же тогда делать, если нужно транспонировать диапазон ячеек, но при этом сохранить интерактивность данных?

Для этих целей есть прекрасная функция ТРАНСП.

Чтобы использовать функцию ТРАНСП вам нужно знать, что эта функция является функцией для массивов, соответственно логика ее работы немного отличается от обычных функций.

Чтобы транспонировать горизонтальный диапазон в вертикальный и наоборот, с сохранением интерактивности данных, делаем следующее:

1. Посчитайте количество вертикальных и горизонтальных ячеек диапазона, который вы хотите транспонировать
2. Выделите диапазон для вставки транспонированной таблицы с таким расчетом, чтобы выделенный диапазон был равен количеству ячеек исходной таблицы или больше
3. Введите в строке формул формулу =ТРАНСП(массив ячеек исходной таблицы)
4. Внимание! На этом этапе вместо привычного Enter нажмите Ctrl+Shift+Enter

Результат - транспонированная таблица с поддержкой интерактивности ваших расчетов.