Macro Stock Data Analysis

Vbalearner85

Board Regular
Joined
Jun 9, 2019
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
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 - Stock name - will be similar to data worksheet name "ACC" and is same in entire column until data range
Each stock has its own Data worksheet with similar structure and in total around 200 data worksheets
Column B - is Date and time stamp of data record time
Column H - Range (will have values only as HR,NR or LR and will be consecutive until switching to any of other 2 options)
Column J- is Move - can be positive , Negative or 0 numeric
Column K- is Timer - timer for measuring time of consecutive move in hh.mm.ss format for which any of column H option repeat consistently. Can be consecutive secs or might be missing some secs
Data can run into thousands of lines for each worksheet of this data.
There are some misc./other data groupings in between these columns, not relevant for this analysis

Expected Data Analysis (on Right hand side of same data starting from row 1
Stock Name- same as Column A
Time from- Time from column B- corresponding to which range LR-NR-HR starts-have 1st occurrence
Range-Range(LR/NR/HR) for above step from column H
L-Move - Lowest Range for each of the instances of LR or NR or HR
H-Move - Highest Range for each of the instances of LR or NR or HR
TimerEndRange-Timer(column K) value where each instance ends (LR or NR or HR ) has last occurrence
Data Analysis can be created on each stock worksheet on the right (suppose starting at column Z-row 1.

Regards,
PK
 

Attachments

  • Macro Data analysis.PNG
    Macro Data analysis.PNG
    43.2 KB · Views: 10

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Threads
1,127,382
Messages
5,624,367
Members
416,025
Latest member
VN5968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top