Automatically Create Time Series Segments in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2842196%22%20slang%3D%22en-US%22%3EAutomatically%20Create%20Time%20Series%20Segments%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842196%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20found%20a%20way%20to%20automatically%20develop%20statistically%20significant%20time%20series%20segments%20within%20Excel%20for%20visualizing%20trends%20over%20time.%20Let's%20say%20you%20have%20100%2C000%20customers%20for%20which%20you%20have%20sales%20data%20in%20every%20month%20this%20year%3A%20January-December.%20Normally%2C%20we%20just%20create%20a%20simple%20line%20graph%20to%20plot%20the%20trend%20in%20overall%20sales%20for%20each%20month%20among%20customers.%20However%2C%20I've%20developed%20a%20template%20that%20will%20first%20analyze%20your%20data%20and%20group%20customers%20based%20on%20their%20sales%20pattern%20over%20the%20past%2012%20months.%20So%2C%20instead%20of%20just%20one%20line%2C%20there%20may%20be%205%20lines%20on%20your%20graph%3A%20each%20one%20representing%20a%20group%20(statistically%20significant%20time%20series%20segment)%20of%20customers%20and%20their%20behaviour%20over%20the%20past%2012%20months.%20My%20template%20seems%20works%20to%20very%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2842196%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2847957%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20Create%20Time%20Series%20Segments%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2847957%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F968123%22%20target%3D%22_blank%22%3E%40cool2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eare%20you%20going%20to%20share%20it%20with%20the%20community%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhat%20if%20the%20organization%20has%20a%20pig%20farmer%20client%20and%20a%20software%20development%20client%2C%20you're%20saying%20your%20tool%20can%20compare%20bacon%20sales%20and%20software%20subscription%2Fsales%20and%20do%20the%20same%20for%20both%20based%20on%20the%20sales%20trend%3F%26nbsp%3B%20Wow%20that's%20awesome%20do%20share%20your%20tool%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2848004%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20Create%20Time%20Series%20Segments%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2848004%22%20slang%3D%22en-US%22%3EYes.%20Let's%20say%20your%20pig%20farmer%20client%20has%20monthly%20sales%20January-December%20for%2C%20let's%20say%2C%20100%2C000%20customers.%20When%20you%20click%20a%20button%2C%20my%20template's%20algorithm%20sifts%20through%20each%20month%20and%2C%20in%20the%20end%2C%20there%20are%206%20time%20series%20segments%20in%20terms%20of%20sales%20among%20customers.%20Now%2C%20let's%20say%20you%20take%20monthly%20sales%20data%20for%20100%2C000%20customers%20of%20your%20second%20client%2C%20the%20software%20development%20client%2C%20and%2C%20you%20click%20a%20button%20on%20my%20template%3A%20there%20may%20be%20only%203%20time%20series%20segments%20in%20terms%20of%20sales%20among%20customers.%20So%2C%20when%20you%20compare%20trends%20pig%20client%20vs.%20software%20development%20client%2C%20then%2C%20you%20may%20see%20completely%20different%20patterns%20or%2C%20perhaps%2C%20there%20may%20even%20be%20some%20similar%20ones.%20I%20have%20not%20yet%20posted%20it%20in%20my%20store%2C%20however%2C%20I%20do%20have%20a%20free%20trial%20template%20you%20can%20try%20on%20one%20column%20of%20data%20to%20see%20how%20each%20column%20is%20analyzed%20by%20my%20template's%20algorithm%20before%20doing%20the%20final%20time%20series%20pattern%20analysis%3A%20%3CA%20href%3D%22https%3A%2F%2Fcool-number-crunching-template.sellfy.store%2Fp%2F95npu9%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcool-number-crunching-template.sellfy.store%2Fp%2F95npu9%2F%3C%2FA%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have found a way to automatically develop statistically significant time series segments within Excel for visualizing trends over time. Let's say you have 100,000 customers for which you have sales data in every month this year: January-December. Normally, we just create a simple line graph to plot the trend in overall sales for each month among customers. However, I've developed a template that will first analyze your data and group customers based on their sales pattern over the past 12 months. So, instead of just one line, there may be 5 lines on your graph: each one representing a group (statistically significant time series segment) of customers and their behaviour over the past 12 months. My template seems works to very well.

5 Replies

@cool2021 

 

are you going to share it with the community?

 

what if the organization has a pig farmer client and a software development client, you're saying your tool can compare bacon sales and software subscription/sales and do the same for both based on the sales trend?  Wow that's awesome do share your tool

Yes. Let's say your pig farmer client has monthly sales January-December for, let's say, 100,000 customers. When you click a button, my template's algorithm sifts through each month and, in the end, there are 6 time series segments in terms of sales among customers. Now, let's say you take monthly sales data for 100,000 customers of your second client, the software development client, and, you click a button on my template: there may be only 3 time series segments in terms of sales among customers. So, when you compare trends pig client vs. software development client, then, you may see completely different patterns or, perhaps, there may even be some similar ones. I have not yet posted it in my store, however, I do have a free trial template you can try on one column of data to see how each column is analyzed by my template's algorithm before doing the final time series pattern analysis: https://cool-number-crunching-template.sellfy.store/p/95npu9/
So you're just really soliciting subscriptions in the community
No. As you can see, I have a full resources section on my site that is free for anyone who works in Excel. And, the free trial template is a way to educate analysts about how Excel can apply - visually using Excel-based algorithms - statistical theories like the boxplot to solve business analytics issues. And, yes, after, if people want, they can buy a subscription to templates. But, no, there's lots of free stuff on the site too re: VBA and Excel resources.
Here is the resources link on the site which is at the top of my navigation on home page. It references many links - mostly from Microsoft Excel and VBA site resources: https://cool-number-crunching-template.sellfy.store/resources/