Automated Analyzer

[NewPuppy]

Hi,
In my Excel is added addon/plugin called "bex bw" (NONenglish language, a lot of custom NONenglish filters) whos main purpose are very complicated filtering and refreshing datas based on new (or edited) datas/values in the same Excel files updated (in realtime with one click done only on file opening) by users who also have in network area access for such file and who have resources based on updates of files can be done. However my question doesn't have much to do with "bex bw" addon anyway.

I would like to ask: Does exist any excel's addon (or any kind of software that can use Excel file extensions) which could analyze every single filter's (every single piece of chart's axis) parameter with every single cell (all columns/lines) within the same Excel file? So analyzed would be every cell with every other cell, including any and all (one by one) filters? I know such analysis would take weeks of frozen pc, operating on that task, due to many custom filters, and every single filter containing hundreds of parameters, every parameter containing thousands or even tens of thousands of cells, even in different worksheets within the same excel file. However such addon/software would save me months of work.

I think I would clarify my question the most with defining ultimate goal of such addon/software: I would like get reports of uncommon variations/deviations seen on charts so I could get answer on the question similar to "Why is particular line in graph (or particular datas in tables) different/unusual than others?" or answer on the question similar to "Because of parameters of which filters and which conditions are some values in tables so much different than other ones?"

I tried to search a lot for such:



"Filter Analyzer"
"Cell Reporter"
"Tables Analyzer"
"Variations/deviations Researcher"
"Uncommon values Looker"
etc

But no luck so far. Some tool similar to this would really save minimum few months of work.

***NOTE: My question is more based on general Excel and NOT just "bex bw" addon where are my thousands of filters with tens of thousands of parameters, each containing hundreds of thousands of values (so quantity of analyis would go by far above 1 billion) so Please don't ignore my question just because you might not use/have "bex bw". I am asking for Excel in general.

[NewPuppy]

hmm... hello?

[BobBridges]

<chuckle> Good evening, newpup. Speaking only for myself, I didn't answer because I don't have an answer. I don't know much about the available add-ons to Excel, except one that Microsoft provides. And speaking or at least guessing for the others, I imagine they didn't answer for the same reason.

But since you're asking, let's at least try to give you some feedback. Your question, I think, is this: "I have some charts and graphs that are the result of tens of thousands of data points. Are there any add-ons to Excel that will analyze the contents of these cells and call my attention to those cells which cause a chart, or portion of a chart, to depart drastically from whatever norm has been established by the others? I realize this analysis would take a long time."

I probably didn't do a good job with your question, and if so I freely admit it's because I didn't understand your question all that well. But it sounds like you need an add-on that will save you a ton of work by doing the analysis in your place. Now, computers can do analysis much, much faster and more reliably than humans, so that part of your question is right on point. But they invariably have to be told how to do that analysis first. You cannot skip that part—and it sounds very much to me as though that's what you're attempting.

If you can describe exactly what sort of analysis will tell you what you need, then maybe you'll find there is already such a program out there for you; and if not, you (or someone) can write one to do what you need, and it will still save you months of work. But you gotta describe what you need first. Something like this: "What causes the blip in the 'JIT offset' line between 2014 and 2017 in chart #7? To know that, I need to find the average values on worksheet "JIT targets", columns F:H and AB:AD, and see where the data exceed the mean values by more than 150% for at least three consecutive rows." Obviously I'm just making that up; the point is that you have to know what it takes to "analyze" the data. Once you've figured that out you (or someone) can tell Excel to do it for you in seconds or hours instead of weeks or months. But until then, all any of us can do is ask helplessly "but what analysis do you want to perform?". And the computer will do no better.

[macropod]

Originally Posted by NewPuppy

Does exist any excel's addon (or any kind of software that can use Excel file extensions) which could analyze every single filter's (every single piece of chart's axis) parameter with every single cell (all columns/lines) within the same Excel file? So analyzed would be every cell with every other cell, including any and all (one by one) filters?
...
I would like get reports of uncommon variations/deviations seen on charts so I could get answer on the question similar to "Why is particular line in graph (or particular datas in tables) different/unusual than others?" or answer on the question similar to "Because of parameters of which filters and which conditions are some values in tables so much different than other ones?"

There are many statistical analysis packages available for Excel. Before you can even choose one, though, you need to know what sort of an analysis you need to run and how to interpret the results. Then it's pretty much just a matter of choosing whichever package provides the best tools within your budget. There is no point in simply throwing a set of statistical analyses at data for which the methods are inappropriate or for which you don't have all of the required data. And, of course, you need to specify the analysis inputs, parameters and filtering criteria for each and every run.

Depending on the package you choose, you may be able to automate that for different data sets, but you will have to specify all of those elements as part of the automation process. in other words, the only time saving might be in the ability to run each statistical analysis back-to-back, rather than having to wait for you to input the details for each one. There can be advantages to either approach. If you do them iteratively, you can look at the results from each pass and modify the way you do the next analysis so that useless analyses are avoided. Ultimately, though, all any stats package can do is analyse the data; none can determine the results' relevance to you.

[NewPuppy]

My main worry is that everything I am doing is inside Excel's addon called BW (Business Warehouse). This have some many disadvantages but inside those disadvantages also some advantages:

Disadvantages:

- with full respect to you, macropod and everything else (please do not take this as anyhow offensive statement): most of Excel specialists won't know what BW addon is and therefore might not even be willing to answer.

- if I, by wonder, manage to find someone very familiar with BW addon, my addon is so much customized/personalized, which I haven't done (customizing - programming, its NOT just own language), that it might get confused even BW expert. Extremly custom filters with extremly custom parameters, even custom buttons like Cancel, OK, Saving, Editing, Right Click menus, Windows, Add, Remove etc. Everything is changed and personalized.

- no idea how would be possible that tool I am searching for would access and analyze the parameters/values in CUSTOM windows, specially the ones Not used - they might still effect used data.

Advantages (!):

- entire BW is used inside Excel only so Excel rules should still work (general rules)

- if I knew how (but I don't so please tell me) I think I always have possibility to check whether or not filters, their parameters and values can be accessed and checked via regular Excel methods of checking/accessing filters. I am just worried that Excel won't be able to access the addon since it seems to be completely isolated (but still located and used only within Excel). However disadvantage of this advantage is actually third disadvantage said above starting with "no idea".

BobBridges yes I am searching tool that will do "everything versus everything else" analysis but, I apologize for correcting you, kindly note that defining WHAT to analyze might not save any time at all. Everything needs to be compared (i purposely used word 'compared' and not 'analyzed' so I can be understandable more) with everything else and, to answer your comment, every way of comparison has to be performed. Everything that might automatically report (obviously without me manually doing the charts) uncommon relations. So again, all/every possible analysis must be runned.

macropod regarding your comment:

"none can determine the results' relevance to you"

All I would choose is excel file and what is counted as Out Of Boundary. So what is counted as being Unacceptable which is what I want to have reported BUT this doesn't mean the tool I am looking for would compare just those filters related to the subject of what I would define. Everything needs to be included. Everything, even the parameters/values NOT used because they might still effect the ones used.

EXAMPLE:

I would just compare everything (all worksheets in one excel file - yes i know it would take long time processing) in progression levels similar to:

Step 1: Comparing every filter (BW's one inside Excel so custom one and not Excel's one) to every other one

Step 2: Comparing every single parameter in every filter with every single parameter in every other filter as well as same one (if chosen or not doesn't matter)

Step 3: Comparing every value in every parameter of ONE filter with every value in every parameter of the SAME filter

Step 4: Comparing every value in every parameter in other (all others) filters with every remaining values in every remaining parameters in other filters.

So briefly: Comparing everything with everything. Every value with every/any other one. So I would get out of ordinary (nonclassic/weird/unwanted) values (why is some value too high or too low) automatically. Of course I would need to define the boundaries - where is something still normal and where "out of ordinary". Or preferable way: I would have automatically shown something that is uncommon BASED ON other values.

ONE example (i could list millions but I will just one - ONLY example, im actually doing this for totally different, engineering, project for manufactory) what would I expect from such automated analysis:

If I have 5 filters:

- country
- age
- genre
- area of interest
- religion

With different parameters (***showing only two, for genre, area of interest, religion, it would be similar logic):

country: europe, asia, africa,
age: between 20 and 30, between 31 and 40, between 41 and 50

With values (*** see note above):

country: germany, france, england, scotland, italy, poland, japan, kuwait, nigeria, egypt
age: 20, 21, 22, 23, etc

actually I have many more values/parameters/filters in BW but its just example. Then the tool I am asking for would compare everything with everything else based on given boundaries/criterias that would be defined inside the tool/software/whatever im asking for/excel's addon/etc:
compare (named as vs): country vs. age, europe vs between 20 and 30, europe vs between 31 and 40, europe vs between 41 and 50, asia vs between 20 and 30,..., germany vs france, germany vs england germany vs scotland,..., france vs england, france vs scotland,..., germany vs 20, germany vs 21 and so on and so on. So everything with everything.
So in such way I could easily get info why is some filter varying so much, because of what is varying, what could be done (which parameter/value used) so it won't vary too much, where are values (e.g. expenses in particular country for particular age) too high and many many more.

[macropod]

Yes, I'm aware that the Business Warehouse (BW) database has a reporting tool named Business Explorer (BEx).

That aside, it seems you're using a highly customised product whose customisations and capacities you don't understand. I'd suggest discussing those issues with whoever is responsible for BEx maintenance & programming in your organisation and find out. If no-one there can help you, contact BW.

If you're considering any form of automation, you'll need to learn how to code the BEx object model (if it has one). Again, BW should be able to provide some advice. If BEx doesn't have a programmable object model, you'll probably have to investigate alternatives.

Once you've done that, you'll still have to program whatever package you use. Simply saying:

All I would choose is excel file and what is counted as Out Of Boundary. So what is counted as being Unacceptable which is what I want to have reported BUT this doesn't mean the tool I am looking for would compare just those filters related to the subject of what I would define. Everything needs to be included. Everything, even the parameters/values NOT used because they might still effect the ones used.

is nonsense. Query parameters do not affect the data and you can't analyse what you haven't defined as a query parameter. If, as you suggest you're simply going to "compare everything with everything else" regardless of the parameters specified, what you're really saying is that you want a parameter-less analysis. Can't be done. And, if you specify every possible permutation and combination as the parameters you'll never get meaningful results.

[NewPuppy]

There is no way I could discuss with my organization what I am asking here. Noone knows that, thats why they are wasting time with manual analysis.
I apologize but I disagree on your comment saying I cannot analyze what isn't defined. In above example, I haven't defined color of skin, favourite sport, favourite book, etc. However it is very possible that (random chosen SUBexample on previous example) people, whos favourite sport on parameter ''Indoor Team Sports'' (parameter of filter) is Volleyball (value), are too many (too many persons) and cannot accept all of them for (randomely saying) membership from X country (given filter) in Y continent (given parameter of filter).

Here I just proved you what isn't defined can have major impact on results. Surely I must input what I am interested for BUT when I said ''what isn't defined'' I was referring to all high number of filters, their parameters, values of those parameters that are NOT enabled to be filtered. With ''to be filtered'' I mean where the datas on worksheet are shown only if meeting criteria of enabled filter. But as said I know I definitely have to define what I am interested for. However definitely must be compared everything.

However defining what I am interested for (=getting report from what data I want to get in order to see out of ordinary related data, one filter/parameter/value with another for purpose of getting the answer on question ''Where further work/improvement must be done?'') can be giant trouble if I want to see in general for entire Excel file what is going on. Even such definition of interest might be bigger waste of time than doing this procedure manually (without tool I am asking for) - I might need to define hundreds or thousands of interests (relations: e.g.: What is causing the Sale to be Reduced In particular month and year in X country) with same amount of defined boundaries (e.g. Sale is reduced if it goes below $500,000). The answer could be wrong material (filter = material) on wrong location (filter = manufactory's location) used, although I wouldn't filter the data based on those two filters.

Anyway my question still remains. Which tool could I use for this? Obviously tool has to be able to access everything from extremly isolated (but entirely located within Excel, except files aren't opened from hard drive but from some internal networking point) and customized addon: BW.

[macropod]

It seems to me you don't really understand what it means to define a parameter. If you don't define something as one of the analysis parameters it won't be analysed; it's as simple as that.

[Pecoflyer]

This must be the first thread I encounter in a forum from which I don't understand a thing. ( not that it bothers me, though)

[NewPuppy]

Originally Posted by macropod

It seems to me you don't really understand what it means to define a parameter. If you don't define something as one of the analysis parameters it won't be analysed; it's as simple as that.

I can already define parameter inside BW. If I would be defining parameters in tool I am asking for and still waiting for accurate reply/answer, no time would be saved.

[BobBridges]

NewPuppy, I apologize for the delay; I was away for a while and didn't realize this conversation was still going on. If you like, we can try to discuss this a bit more. Paul may be right, but while I read your 12-19 04:00 post (and reread, and rereread it) I come to suspect that maybe the problem we're having is in a difference of understanding about the word "compare".

At one point you said you might (for example) need to "compare country vs. age", and went on to name a bunch of examples: "europe vs between 20 and 30...asia vs between 20 and 30...germany vs 20" and so on. Now, in statistical analysis there's a thing called "cross-tabulation", and it sounds to me as though this is what you're talking about. For example, if I have 8000 responses to a survey, I can ask my program to "cross-tabulate" the positive responses to question #21 by age and race, and it'll create a table something like this:

            18-21 | 22-35 | 36-50 | 51-75 |  Null | Total
          +-------+-------+-------+-------+-------+------
Caucasion |  4.5% |  3.2% |  1.2% |  4.1% |  2.5% | 15.5%
          +-------+-------+-------+-------+-------+------
Black     |  4.0% |  2.6% |  0.8% |  7.9% |  0.8% | 16.1%
          +-------+-------+-------+-------+-------+------
Asian     | 14.5% | 12.6% |  0.0% |  1.0% |  0.5% | 28.5%
          +-------+-------+-------+-------+-------+------
Other     | 12.2% | 11.0% |  8.7% |  4.2% |  0.1% | 36.2%
          +-------+-------+-------+-------+-------+------
Null      |  0.2% |  0.5% |  1.5% |  1.4% |  0.1% |  3.7%
          +-------+-------+-------+-------+-------+------
Total     | 35.3% | 29.9% | 12.2% | 18.6% |  4.0% |100.0%

Is this the kind of "comparison" you had in mind? If so, then we probably misunderstood you at first—I did, anyway—and Paul may be able to suggest something. Me, for this kind of analysis I'd look at SPSS or SAS or one of the other packages that are designed for this kind of analysis.

[NewPuppy]

Bob Thank you for reply. You asked me if your example if what I meant. No, not at all. The reason for this is because all such parameters can already be compared inside BW (excel's addon). As soon as I am required to choose WHICH parameters must be compared (parameter1 vs parameter2 OR filter1 vs. filter2 etc) then this is pointless because NO time is saved. I can already do everything like this inside BW and much more.

The main feature of the tool I am asking for is possibility that I don't need to define WHAT to compare because everything must be compared:

- every filter with every other filter (level one of comparison)
- every filter's parameter with every other filter's parameter (level two)
- every filter's parameter's value with every other filter's parameter's value (level three)

even the filters/parameters that are NOT enabled for view of table must also be compared. This BW addon, not just it is isolated in excel but also very customized.

The goal of having such tool I am asking for is simply defining only WHAT counts as being out of order. Thats all. However I would expect from tool some reports where I wouldn't need to define numerical value: ''what counts as being out of order'' for example (only random chosen example) in report of the tool I would like to see answer on:

''Why are being too many newcommers refused in particular company with job applications?''

I wanted to show in YOUR example (table) what would I define in the tool I am looking for but you are showing only 2 filters and 8 parameters. Impossible to provide case on your example - im having few thousands filters and few tens of thousands of parameters. If I try to anyway, just to be as much as possible understandable, I would define in the tool (in your example) something like (note: with your comment ''positive responses'' I am assuming something like well learned lecture related to the lessons topic number 21 as you said):

''What is causing asian people from 36 to 50 years old to have so many troubles in understanding the lectures on topic 21 comparing to asian people from 18 to 21 years old - could be wrong books used? could be they missing during the class? why would they ignore the class? why 18 to 21 years old attended the class then? etc''

But all those answers can be gathered obviously only from filters (enabled or not, doesn't matter). So for the latest question in quoted text there should be something like:

filter: class attending (further directly related to your two filters)
parameter1: reason/goal of attending the lecture
parameter2: interest for attending the lecture

Hopefully now im little more understandable. Once again: In the tool I am looking for I should only need to define:

If numerical definition needed: Define the boundaries
If numerical definition NOT needed: Defining what kind of report I want

BUT I definitely don't want to define what filters/parameters to compare. Otherwise for sure no time is saved.

[BobBridges]

Ok, so that idea wasn't right. But I still want to understand this, so maybe I should look more closely at your example and ask for details about it. In your example, the question you want answered is "'With regard to company X, why are too many applicants being refused employment?". I infer that you have a database of job applications to many companies, and you see that for many companies there's an average rate of refusal vs acceptance; but in one particular company you see that the percentages are far from the average, and you want your analysis to discover why. You hope that by examining all the data about these companies and these job applications, you might be able to spot a relationship between vectors, or a combination of relationships, that will make the matter clear. Am I right about that? So you're hoping for a product that will search through the many, many thousands of data looking for a pattern, or rather a departure from the pattern, that may explain it?

[NewPuppy]

It was just an example. I could and should get thousands of different reports from automated tools I am asking for but obviously every piece or report should contain (and it does) elements/parameters/filters/values in the BW (Business Warehouse) - very customized Excel's addon.

Yes you are right - I would like to get reports with method ''comparing everything to everything else'' without needing to define what should be compared. As soon as I define, time is completely wasted because I could define in BW addon already. The point is to NOT need to waste time and define parameters AND (!!!) draw charts - everything needs to be automated. This is the tool I am searching for. I should only be required to define what I am interested for and NOT what to compare.

Answer to your last sentence in your latest message: yes

[BobBridges]

Well, I may be wrong, but I think you're hoping to find a program that will think for you. Programs, unfortunately, don't think; they just calculate, once someone explains to them how to do it. You would have to define at least part of the problem for the program.

Put it this way: If you were to write such an add-on yourself, what (exactly) would you tell it to do? You can define to your program what constitutes a data vector (age, nationality, profession, whatever), and tell the program to take the vectors in every possible combination of pairs. But here's the question: Once your program is looking at two vectors—educational level and religion, perhaps—what should your program do with them?

So far we've just said "compare", but for a program to be able to follow instructions we have to be more specific. What about the data are you looking at? What would you do to advance your analysis?

I've got a notion that despite your insistence that you want to compare "everything to everything", what you have is a particular datum—a bunch of people who answered 'yes' to question 13, for example—and you want to find a correlation between that answer and one or a combination of demographic vectors. A really exhaustive program, therefore, would calculate the correlation between question 13 and each demographic vector; then between question 13 and each combination of two demographic vectors; then between question 13 and each combination of three demographic vectors; and so on. Then it would list the correlations in descending order of absolute value.

If so, you would have to define to your program at a minimum a) which are the demographic vectors and b) which question(s) on the survey you want analyzed in this way. But I'm not at all convinced that this is what you want, still.