Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Live data percent

0

How to find percentage of live data, which is updated every minute in comparison to old data which was just 1minute before?

Answer
Discuss

Discussion

This depernds upon how you keep and update your data. In principle, you probably can't intercept the new data. Therefore you need to preserve the previous data so that you have it for comparison. Since the update will over-write all existing data this preservation must take place before the update is triggered. 
There will be no answer to your question unless you work on it really hard.
Variatus (rep: 4889) Nov 6, '20 at 10:24 pm
Agreed, If the update can be modified to first copy the last value into an adjacent cell reserved for the previous value (perhaps in a hidden column), then a third static cell could be used to calculate the latest percentage (=updated value*100/previous value).

@Variatus- if the above approach can't be used, might a value be stored in a Public variable using the Application.WorkbookAfterXmlImport event then the old/new  % calculation made before that Public variable is overwritten with new on the next Application.WorkbookAfterXmlImport event (ready for the next such calculation)?
John_Ru (rep: 6092) Nov 7, '20 at 5:10 am
Add to Discussion

Answers

0

The Application.WorkbookAfterXMLImport event, which you suggest, looks like a good idea. So, you might end up with this code flow. Start by having two tables (or two sheets), A and B.

  1. When you run the download data are written to your table A.
  2. The Application.WorkbookAfterXMLImport event fires when the download is done.
  3. This event triggers a comparison between table A and table B, writing the difference to dedicated columns in table A.
    1. On first run table B is blank and therefore no comparison is carried out.
    2. When this is done, the same procedure copies all of table A to table B.
  4. On the next run table B holds the values from the previous run.
Discuss

Discussion

Nice! Thanks Variatus for expanding to a full outline solution 
John_Ru (rep: 6092) Nov 9, '20 at 4:48 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login