Live data frequency counter

[Vbalearner85]

Hi,

Just wondering if excel has any vba function for counting occurrences/frequency for numerical data - more than or less than any number(suppose it is 1) for live dynamic data(changes per sec/min)..like in a column

For example -
1.if data in column cell "A2" has number more than 1 for 5 times in a min for live Dynamic data....in next column cell "B2"..it should show "5: and add 1 to it for every such occurrence
2) Similar to step 1, it can show frequency for data less than 1, in column cell "C2" for column data "A2"
2) Steps 1 and 2 can be applied to column range (A2:100), and have independent frequency counters for each cell in range

Regards

 

[Legacy 456155]

What method are you using to pump the "live dynamic data" into your spreadsheet? RTD function? DDE? Other? The reason I ask is that there are differing methods for dealing with each of these.

 

[Vbalearner85]

I am using API-- set of REST-like HTTP APIs ..

 

[Vbalearner85]

Another option is having data updated/Copy via timer macro every sec from live data feed sheet. If that simplifies the frequency macro

 

[Legacy 456155]

Another option is having data updated/Copy via timer macro every sec from live data feed sheet. If that simplifies the frequency macro

Sure, we can work with that if necessary. But if you are using a query that automatically refreshes every x seconds, there are events available without the need for a timer.
Let me make sure I understand you.

Point = x (1 in your description)
Column A contains the changing data.
Col B is a count of A > Point in last 60 seconds
Col C is a count of A < Point in last 60 seconds

Does each row have it's own Point/Benchmark?
What if the update = Point? Where does that go?

 

[Vbalearner85]

1)Does each row have it's own Point/Benchmark? If that is possible yes..as I am using this for my impact analysis purposes....although they can se same also most of the times..but added functionality is always a bonus !!!!...Lower point/Benchmark and upper point/Benchmark which can be set differently for each column A cell is desirable
2)What if the update = Point? Where does that go? I am trying to capture frequency data only where I know "greater than or equal to" or "less than or equal to" benchmarks drives other factors ...so ">=" and "<=" would serve the purpose. There is range for each cell data which I am trying capture and how it is driving second dependent factor data. for example Lower benchmark can be "0.8" and Upper benchmark is "1.05"..so Frequency should be calculated for below
a) Every times data moves into "less than or equal to 0.8 - <=0.8" range from "greater than 0.8 - >0.8" range is counted as 1 frequency addition and so on for every such instance. Please note if data remains in the range <= 0.8 after such instance. Frequency count does not get impacted, count only increases by 1, after re-occurrence of the same event
b) Every times data moves into "greater than or equal to 1.05 - <=1.05" range from "less than 1.05 - >1.05" range is counted as 1 frequency addition and so on for every such instance. Please note if data remains in the range >= 1.05 after such instance. Frequency count does not get impacted, count only increases by 1, after re-occurrence of the same event
c) when data remains in the range 0.8-1.05, no impact expected on the frequency counting. Also time is not limited to 60 secs..but can be until excel remains open
d) My project is to capture i) Frequency as stated above ii) capture "time elapsed" calculator for each instance by using below macro. Which seems working fine by capturing timestamp for each such instance occurence and how long it is retained by formula "=(Now)- timestamp of occurrence"...Not sure if both tasks can be achieved via same macro solution

Private Sub Worksheet_Calculate()
Dim Cell As Range
For Each Cell In Range("A1:A142")
If Cell.Value <> Cell.Offset(0, 8).Value Then
Cell.Offset(0, 1).Value = Now
Cell.Offset(0, 8).Value = Cell.Value
End If
Next
Columns("B:B").EntireColumn.AutoFit
End Sub

Regards
I hope I am not complicating things by overexplaining..Thanks

 

[Legacy 456155]

I hope I am not complicating things by overexplaining..Thanks

Not at all. Details are welcome.

I think I understand you except for the "time elapsed." My guess is that you mean the time elapsed from the last move to the current move from:
1. >Low to <= Low (Col F)
2. <High to >= High (Col J)
OR is time elapsed a single field that accounts for either 1 or 2 ?

 

[Vbalearner85]

yes visualization is something I expect to look like.

OR is time elapsed a single field that accounts for either 1 or 2 ? Yes

Time elapsed is how much time(in hh:mm:ss format) the data remains in current threshold after entering in the range for live data. and would restart from 0 every time new occurrence happens

 

[Legacy 456155]

Macro Stock Data Analysis

Hi, I am trying to automate large amounts of data analysis. Data structure and expected data analysis snapshot is attached. I believe macros can do it, but not quite competent to achieve this..any help will be greatly appreciated!!! Assumptions Data starts from Row 2- Headers in row 1 Column A...

www.mrexcel.com

 

[Vbalearner85]

Hi Dataluver,

I have resolved the timer(as mentioned by me above) to my data needs macro.

Only pending issue is frequency counter for every move from LM to HM and vice-versa

was above reference/link to my other macro question related to that??Please advise

Regards,
PK