Forum Discussion
Accounting advice needed
Hi team,
we get a quarterly downloadable report from our provider which has a bunch of data points such as product codes, number of customers, net income, income share etc. This comes in a big table with about 38 columns (data points) currently 350ish product codes in the rows (which will grow as we increase our catalogue). I'd like to create a good way to automate and summarise our financial reporting such as easily creating annual total income, annual income per product code, mapping trends etc. I think I could do this as a one off using SUM, SUMIF, LOOKUP etc. The problem is I don't want to have to do it all again when we get emailed the next spreadsheet. How can I create a workbook that I can drop the new data table into and have it automagically sum into annual and historical totals?
If anyone knows a how-to guide or short course that covers this kind of thing I'd love to know about it.
- Have you used Power Query or as it is called now "Get & Transform"? The quarterly report that you get, is it in the same format each quarter? If it is, you could always create a new query that at the most simple will grab the file and append the new quarterly data to the past data. Are you using pivot tables to summarize your data? You can view
https://support.microsoft.com/en-us/office/power-query-for-excel-help-2b433a85-ddfb-420b-9cda-fe0e60b82a94
2 Replies
- Have you used Power Query or as it is called now "Get & Transform"? The quarterly report that you get, is it in the same format each quarter? If it is, you could always create a new query that at the most simple will grab the file and append the new quarterly data to the past data. Are you using pivot tables to summarize your data? You can view
https://support.microsoft.com/en-us/office/power-query-for-excel-help-2b433a85-ddfb-420b-9cda-fe0e60b82a94- Hi Scott,
this sounds like a good thing to look into. However I'm having trouble getting Power Query to work on my Mac. The app has Power Query guidance in the help guides, but the options are not there in the menus. Any other ideas on how I can achieve this?
