Icon

Microsoft Financial Statement Prior Year Tie Out

This workflow reads the the current and prior year 10K, identifies incomestatement lines and ties out agrees the prior year balances in the current yearto the prior year.This is a frequent procedure that is done by financial reporting and auditingteams to make sure that the prior period is stated correctly.Get PDF input files for 2021 and 2020 Microsoft 10K from https://www.microsoft.com/en-us/Investor/sec-filings.aspx Current YearPrior YearSplit by “\n“ (newline)character and createa ListCellUngroup the list cell into individual rowsRemove suffix“_SplitResultList”Restore the originalcolumn orderNode 15Node 16Identify FS AmountsReplacenew line char with -Identify Income StatementRemoveUnusedGroupsFilterISFilterBlankColumnsExtract Period Ending DatesFilter by ValuesExtractCol for mappingExtractCol for mappingFilterby Year endRemove DupsTransposefor mappingJoin old and new col namesRenamevalue columnsto year endNode 38Removerows which are notvaluesIdentify Line CaptionRemoverows which are notvaluesRemove Missing ValuesFilterBlankColumnsExtract Period Ending DatesFilter by ValuesExtractCol for mappingExtractCol for mappingFilterby Year endRemove DupsSplit by “\n“ (newline)character and createa ListCellUngroup the list cell into individual rowsTransposefor mappingRemove suffix“_SplitResultList”Restore the originalcolumn orderJoin old and new col namesRenamevalue columnsto year endNode 59Node 60col namesvarRemoverows which are notvaluesIdentify FS AmountsReplacenew line char with -Identify Income StatementIdentify Line CaptionRemoverows which are notvaluesRemoveUnusedGroupsFilterISRemove Missing ValuesJoin CurrentWith PriorRenameColumnsfor Rule EngineVariablesRenameColumnsfor Rule EngineVariablesCreate the first Rule to compare period period 1Filter by First and Second RulesAdd $ to column namefor Rule EngineFilter by prior period columsPass colsinto String Manipulationto create rulesPass rulesinto RuleEngine NodeNode 91Node 97Create the second Rule to compare period period 2Node 101Remove ISFul MatchRemove IS Full MatchNode 105Tika Parser Tika Parser Cell Splitter Ungroup Column Rename(Regex) ReferenceColumn Resorter ExtractColumn Header Transpose Regex Extractor String Replacer Regex Extractor Column Filter Column Filter Missing ValueColumn Filter Regex Extractor Column Filter ExtractColumn Header Column Filter Column Filter DuplicateRow Filter Transpose Column Appender Column Rename Table Rowto Variable Row Filter Regex Extractor Row Filter Row Filter Missing ValueColumn Filter Regex Extractor Column Filter ExtractColumn Header Column Filter Column Filter DuplicateRow Filter Cell Splitter Ungroup Transpose Column Rename(Regex) ReferenceColumn Resorter Column Appender Column Rename ExtractColumn Header Transpose Table Rowto Variable Row Filter Regex Extractor String Replacer Regex Extractor Regex Extractor Row Filter Column Filter Column Filter Row Filter Joiner Column Rename Column Rename String Manipulation(Multi Column) Column Filter String Manipulation(Multi Column) Column Filter Table Rowto Variable Table Rowto Variable String Manipulation(Multi Column) Rule Engine String Manipulation(Multi Column) Rule Engine Column Filter Column Filter Excel Writer This workflow reads the the current and prior year 10K, identifies incomestatement lines and ties out agrees the prior year balances in the current yearto the prior year.This is a frequent procedure that is done by financial reporting and auditingteams to make sure that the prior period is stated correctly.Get PDF input files for 2021 and 2020 Microsoft 10K from https://www.microsoft.com/en-us/Investor/sec-filings.aspx Current YearPrior YearSplit by “\n“ (newline)character and createa ListCellUngroup the list cell into individual rowsRemove suffix“_SplitResultList”Restore the originalcolumn orderNode 15Node 16Identify FS AmountsReplacenew line char with -Identify Income StatementRemoveUnusedGroupsFilterISFilterBlankColumnsExtract Period Ending DatesFilter by ValuesExtractCol for mappingExtractCol for mappingFilterby Year endRemove DupsTransposefor mappingJoin old and new col namesRenamevalue columnsto year endNode 38Removerows which are notvaluesIdentify Line CaptionRemoverows which are notvaluesRemove Missing ValuesFilterBlankColumnsExtract Period Ending DatesFilter by ValuesExtractCol for mappingExtractCol for mappingFilterby Year endRemove DupsSplit by “\n“ (newline)character and createa ListCellUngroup the list cell into individual rowsTransposefor mappingRemove suffix“_SplitResultList”Restore the originalcolumn orderJoin old and new col namesRenamevalue columnsto year endNode 59Node 60col namesvarRemoverows which are notvaluesIdentify FS AmountsReplacenew line char with -Identify Income StatementIdentify Line CaptionRemoverows which are notvaluesRemoveUnusedGroupsFilterISRemove Missing ValuesJoin CurrentWith PriorRenameColumnsfor Rule EngineVariablesRenameColumnsfor Rule EngineVariablesCreate the first Rule to compare period period 1Filter by First and Second RulesAdd $ to column namefor Rule EngineFilter by prior period columsPass colsinto String Manipulationto create rulesPass rulesinto RuleEngine NodeNode 91Node 97Create the second Rule to compare period period 2Node 101Remove ISFul MatchRemove IS Full MatchNode 105Tika Parser Tika Parser Cell Splitter Ungroup Column Rename(Regex) ReferenceColumn Resorter ExtractColumn Header Transpose Regex Extractor String Replacer Regex Extractor Column Filter Column Filter Missing ValueColumn Filter Regex Extractor Column Filter ExtractColumn Header Column Filter Column Filter DuplicateRow Filter Transpose Column Appender Column Rename Table Rowto Variable Row Filter Regex Extractor Row Filter Row Filter Missing ValueColumn Filter Regex Extractor Column Filter ExtractColumn Header Column Filter Column Filter DuplicateRow Filter Cell Splitter Ungroup Transpose Column Rename(Regex) ReferenceColumn Resorter Column Appender Column Rename ExtractColumn Header Transpose Table Rowto Variable Row Filter Regex Extractor String Replacer Regex Extractor Regex Extractor Row Filter Column Filter Column Filter Row Filter Joiner Column Rename Column Rename String Manipulation(Multi Column) Column Filter String Manipulation(Multi Column) Column Filter Table Rowto Variable Table Rowto Variable String Manipulation(Multi Column) Rule Engine String Manipulation(Multi Column) Rule Engine Column Filter Column Filter Excel Writer

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: ALIIG
Created at: 2022-06-25
On NodePit since: 2022-10-21
Last update: 2024-04-27
Created with KNIME version: v4.6.0

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!