#google-sheets #pivot-table
#google-sheets #сводная таблица
Вопрос:
У меня есть сводная таблица с совокупным количеством акций для каждой акции и с разными временными метками, когда было произведено изменение портфеля (инвестирование или продажа): https://docs.google.com/spreadsheets/d/1IxdeBriRA9DgVclAWwfrz5ni1bZO94xwh0jFCghTLDg/edit?usp=sharing
Теперь я хочу добавить промежуточные даты (для каждого отдельного дня недели) для временных меток invest и divest, где логически совокупное количество акций больше нуля.
Пример «Apple»: я хочу, чтобы в сводной таблице отображались все даты для каждого отдельного дня недели для Apple с 2013-05-08 по 2014-12-16 с совокупным числом акций.
Пример ‘AT amp; T’: поскольку распродажа акций AT amp; T не производится, мне нужны все даты с 2020-04-06 по сегодняшний день.
Я хотел бы иметь эти два примера со всеми другими запасами в одной таблице, чтобы применить дополнительные функции. Цель состоит в том, чтобы использовать функцию GOOGLEFINANCE для расчета цен на акции за каждый последующий день. Но сначала мне нужны все даты (где количество общих ресурсов> 0).
На втором листе представлены «необработанные данные». Я также в порядке, если есть лучшее решение, чем использование сводной таблицы.
Большое вам спасибо! Приветствует Фабиана
Комментарии:
1. Что вы подразумеваете под «всеми датами»? Цена акций Apple за каждый отдельный будний день с 2013-05-08 по 2014-12-16? Или в конце каждого месяца? Или что-то еще?
2. На первом шаге только даты для каждого отдельного дня недели с 2013-05-08 по 2014-12-16. Но также и для AT amp; T и других акций в одной таблице. После этого я могу легко применить формулу GOOGLEFINANCE для цены акций каждого дня недели.
Ответ №1:
Пожалуйста, смотрите testFile.
Поскольку ваши данные содержат большой период времени, таблица со всеми датами, включенными для всех акций, будет действительно большой и непригодной для использования, поэтому предлагаю выбрать одну акцию и создать для нее таблицу.
Таким образом, на тестовом листе в ячейке B1 вы выбираете название интересующей вас акции. В ячейке B2 выберите, требуется ли период только для исторических данных или для последних.
Следующая формула создает последовательность дат, начиная с первой даты для выбранного запаса и до настоящего времени.
=ARRAYFORMULA(
IF($B$2="Today";
SEQUENCE(TODAY()-MIN(FILTER('Stock Track Record'!A:A;'Stock Track Record'!F:F=$B$1)) 1;1;
MIN(FILTER('Stock Track Record'!A:A;'Stock Track Record'!F:F=$B$1));1)))
Затем следующая формула получает значения из ваших исходных данных.
=ARRAYFORMULA(
IFERROR(VLOOKUP($A$5:$A;
QUERY({'Stock Track Record'!$A:$F};
"Select Col1, Col2, Col3, Col4
Where Col6 = '"amp;$B$1amp;"'";0);2;0);""))
И далее вычисляется совокупное количество общих ресурсов:
=ARRAYFORMULA(IF(A5:A="";"";SUMIF(A5:A;"<="amp;A5:A;D5:D)))
Комментарии:
1. Привет, Олег, большое тебе спасибо за твое решение. Если в вашем тестовом файле будут отображаться все акции с их конкретным диапазоном дат, это будет именно то, что я ищу. Я знаю, что это будет много данных … можете ли вы изменить свое решение, чтобы показать все? Я хочу проверить производительность.
2. @incher91 добавлен в testFile testSheet2. Проблема в том, что с помощью формул он не будет выполнять цикл для каждого запаса и генерировать для него даты, поэтому с первой даты до сегодняшнего дня должен быть большой период. Всего 2752 даты, умноженные на 28 товарных позиций, что означает 77056 строк, что действительно замедляет работу файла. В вашем случае сценарий может быть более подходящим решением.
3. Спасибо, Олег, ты меня убедил. Я думаю, что script — это правильный путь в этом случае. Но ваш 2-й тестовый файл тоже очень хорош!