Call for Excel VBA help: to support effort to clarify/understand FL

[2SleepBetta]

I come to members with a narrow technical request for help that will, I hope, enable me do a post of some value to me and those concerned to know what Resmed flow limitation flags actually signify vs another approach.

My question is, how to write VBA "code?" that will sum up disparate-duration-amplitude clusters of strips making up inspiratory waves as those half waves are distributed vs. varying expiration times (in files with up to 800k rows or 8,000 inhalations). I have an Excel ".xlsm" workbook someone might allow me to put into a dropbox if choosing to look at the question or file for more specifics. 

My project at this stage is centered on finishing numeric integration of the EDF flow rate data. A longtime user--once an Intermediate or better user--of many Excel Worksheet tools, I know no formulaic or practical way to use ordinary SUM or other functions to sum up our doubly various clusters of 0.04-second wide strips comprising one inspiratory wave. I have worked at one time or another with OFFSET, INDEX, MATCH, SUMIF and LOOKUP functions, etc.; there may be some referencing magic I've missed/don-t recall in those or other functions. If all durations were constant, summing up would be simple.

It looks to me like a simple loop programming job with summing to be done for each wave strip in a cluster, row by row, down through FR data until FR sign changes from from plus to minus: there and then simply write the cluster total and zero the accumulator and total assignees. In trying that I stumble tangled up in, er, basic Object, Type, and Range, etc., mistakes.

I am trying to learn and use VBA, but am unsuccessful and embarrassed after many hours invested tangled up in the unfamiliar variety of VBA demands. Online helps abound and my Walkenbach VBA book helps too, though about half of an expanded  new one (2019, his handoff to others, $55 at B&N) is regurgitation of his 2013 book. But one thing hard to find is full illustration of harmonious use of all the required elements in a few real life problems. So far, I have rarely found anything but the narrowest snippets to illustrate a micro point using generic placeholders. Necessary context and side effects? You gotta know/remember. 

So . . . , tech team, thank you for your excellent work in that demanding realm--and for your fine OSCAR product and other new or enhanced AB features. And thank you SuperSleeper and all for this site's reach, quality and my improved health.

Thanks for looking at this whiney post and, in advance, for suggestions.

[Crimson Nape]

My apologies! I didn't think about providing you with a link to free course manuals for various subjects until now. Here is the link to Excel VBA: https://books.goalkicker.com/ExcelVBABook/

I hope it may help. If not, double your money back.

[Gideon]

I'll look at it. I was pretty good at VBA using it for things it was never intended to be used for. I don't fully understand what you are trying to do though.
I can extract data from almost anything Excel

[2SleepBetta]

@ Crimson Nape: Thank you for the PDF on Excel VBA I downloaded.

Gideon, 

Thanks for responding. I attached a figure showing, at left, the two columns of dummy FR data from the dummy BRP EDF file and, at right, an illustration of two columns of sums I need. 

The conceptual task is childish for all but Excel worksheet newbies--as you probably see my difficulty with VBA here--except for varying wave inspiration and expiration durations. For personal examples I used brute force drags of the wave values to see totals at the bottom, as Excel worksheets provide. But no fixed range for the SUM function would work because of wave splitting or over-reach spanning too many waves. Far too many errors in a night of data. 

By way of full but tardy disclosure, I posted much the same question at an Excel forum, hoping for a fast response somewhere after wasting so much time needing to move on. But I got in trouble there for cross posting I truly and unthinkingly lapsed into, though, yes, "on notice" from site rules I had scanned but failed to remember. One thing for sure, this is a much friendlier site for newbies (as I was there) where site mechanics, including message addressing, are much more visible and transparent. I attempted to respond, but had not copied my message which vaporized with a message I needed to make more posts before using that reply system. 

If you need some funnies, I could send you   what I "coded" in VBA. My guess is that, besides declarations (correct ones as needed) and Range IDs, the procedure will be less than about 5 lines.

   

Oops, the "35" in the rightmost column should be "72", Typos, typos.

[Gideon]

Slice bucket formula =IF(C15>0,C15)

AllSlices formula = =IF(C15>0,IFERROR(E14+D15,D15))

That should be close to what you are doing

[kappa]

I would create a column that indicates breath number (based on switch from +ve to -ve and vice versa, possibly with some additional logic to handle jitter or cardiogenic artifacts around the zero point). Then you can use SUMIF or pivot table style calculations.

[2SleepBetta]

Thanks to Gideon for his method post and to Kappa for his speeding the process by refocusing me on sign changes: we have a method for numerical integration of the FR curve from Resmed machine data. The method can be refined and made more accurate, in one or more ways, if desired, by using more accurate numerical integration methods than the mid-point approximation.

The attachment shows the results and it can be condensed.

Notes: The column after the time is actually the product of time, given in the data log, and the flow rate ordinate in the data log. The illustrated  " Time X FR data" values (column B) are just random alternations of numbers  swinging back and forth from positive to negative and sometimes are zero, as those number swings are in the actual data file and breathing being be emulated.


More ideas or suggestions are always welcome, appreciated, and (most likely) needed. Next step is to choose which way to use a moving average vs next wave volume for comparison to the machine FL. We hope to get a better grasp of what small-letter flow limitation is and what a FL flag means in relation to it. One fellow had a macro for doing percentiles. Else?


   

[2SleepBetta]

Here is another call for ideas and help or someone's redirection. 

My goals at present are to understand better what the Resmed (RM) flow limit (FL) means in and of itself, understand what it means in relation to getting better airflow and better sleep, and  work toward a way to summarize FL more accurately and usefully--think of the present Minimum and Medium, if you do understand the 95% or Maximum in OSCAR's summary grid. Something more intuitive is needed, as sheepless' recent thread indicated, something more similar to how AHI, etc., are scored. 

As of now, a couple of sub-goals are to develop and apply the crude start (which I have some confidence in) indicated below and to try to learn and apply a Greek engineer's VBA macro for doing percentiles--for now, possibly--which is online.   It would be great if another member would slog faster or jump out in front and run with the ball. Gideon and kappa have helped a lot.

The graphic holds no surprises in showing how FR and the wave preceding a sliding window of inspiration (volume) waves relate. Seeing it all more clearly is a side benefit from needing and getting some grasp of actual volume flow, which, I expect, will enable a DIY percentiles summary.  One can see why FL are marked, but not always why they are not. The algorithm, as I understand RM and SleepRider, applies fuzzy logic that (my guess here) is mainly to provide fast and appropriate feedback control for the blower to respond to FL, etc., or to back off pressure. As a graphic above shows, individual wave shape and respiration rate (duty cycle?) factors are in play in addition to air flow and flow limitation. The goal is not to replicate RM flagging, but to clearly see how it relates to flow.

The green-colored boxes just call attention to those breathing disturbances that did and did not draw FL flags. 

(I gotta hope that the clutter of red grid lines promote others' visualization of relationships among the curves as much as they help me line related things up. Those are from "Meazure", a free on-screen tool that is a big help like Windows "Snipping Tool", the on-screen"A Ruler for Windows" and "Epic Pen", and "ClipMate"--the latter for taking copies of what is highlighted and storing them for convenient paste-in later (or for conversion to a file or just temporary hold.)


   

[2SleepBetta]

An "antescript" up front here as opposed to a postscript that should be after closing below. A progress--I think--report here. 'Am hoping there are not too many typos and such as I end by pasting-in this post, ready to hit the poting button

Posting these graphics may be premature because more time and study must focus on what seem to be anomalies among the many areas that seem comparable. More about those in my next post.

Again, as a reminder, though welcoming comment on my therapy needs, this post uses my SleepyHead chart of 11/27/2015 and all is focused on trying to understand just what a Resmed (RM) Autoset or VAuto's flow limit flag (FL) means. 

In members' AB-wide discussions the process has helped us learn a lot. Many of us posting on the topic at AB. Some have questioned whether FL is much more than a RM marketing gimmick. I definitely disagree with some criticisms of the FL notion, but those challenges help focus study. My crude graphic comparison, below, begins to show why I see value in the FL information.  

Overall, as I see it, the FL flag means there has been a significant loss of air volume delivery to the mask because of some restriction, or threat of restriction, in our airway that did not meet criteria for scoring an apnea. More narrowly, I believe the Resmeds use and leave a flag for us to see--and, somehow, to come to understand--a reasonable representation their pattern recognition devices/algorithms used to drive PAP pressure changes in real time.

BIGGEST POINT OF POST: The volume reductions shown in the attached minute volume (MV) chart are in fair agreement with my  Resmed Autoset's flagging of FL. FL flags have  meaning I believe they are intended to convey.

I reversed the sign of my MV reductions (reductions vs a specified MV and first stab at filtering) to bring out the similarity in FL and MV profiles if the large remaining artifact-like spikes are ignored.

A couple of key differences in FR vs MV, as in the graphic, arise from known differences in algorithms, the Autoset's and mine:

In its flagging, Resmed factors in shapes of the inspiration curve, especially the presence of "M" tipped inspiration peaks. Also, it uses respiration rate (RR). Both these are understood to be helpful in raising or lowering pressure timely as flow limitation is detected or waning.

Side note: Close inspection of pressure peaks with sharp reductions vs. large FL rise and fall show my Autoset pressure reductions were too soon and sharp, in my high FL case 20151127-28m, at least with my 7-20 and 3 cm settings then. That graphic will be posted in next post if not below. Note that raising EPAP to 7.6 cm later made a huge difference.
 
A few comments follow about detail in the charts, based on what I have learned about my sleep from AB, my own analyses and readings--all aided, over time, with serial acquisitions of and learning from readings, a cervical collar, oximeter, position and agitation sensing accelerometer, and (last October),a Dreem 2 headband (which, IMO, does only a so-so job of detecting WAKE events).

Most LIKELY FL, given all as graphed, is explainable as below:
:
Before the OA's-

1. Always or mostly on my best sleep side, the left, as opposed to then-preference of the right side.

2. Continuous volume loss. Largely, but not all, those come from choosing too high a  baseline volume for the graph axis.  My graph shows "loss of breath" by deducting a breath's volume from the baseline MV I chose for the whole session. (See boxed statement for baseline and filter settings, below or in next post. Gotta get some sleep here, pretty soon and will stop for the night.)

During the OA's-supine.

After the OA's-right side-

1.  Most probably tucking chin long before adopting the cervical collar.

2. Most probably consistently deformed inspiratory flow peaks with long runs "M" tips , which the RM's are, in my experience, very sensitive to--often when only one occurs amid a string of fairly normal rounded waves.

3. As others have mentioned, cardio-ballistic artifact (CBA) effects might account for some of the FL. My workup for the graph was and still is slowed down trying to tease apart inspiratory flow from pronounced CBA, probably all the time after the OA's.  Quite possibly I'm wrong here, but numerical integration of positive-value data shows a lot of CBA. It explains why I filter it out and likely need to do more than in done for this first crude graph. Again, data only occasionally
has zero-axis values and my curve, to date, ignores interpolation for zero-crossing determination at start and end of each inspiratory flow. 

Enough for tonight. Help, criticism or corrections would be appreciated. Course correction is needed where unknown blunders and misunderstandings lie.

   

[2SleepBetta]

Update:

Work is still in progress, but I put this still-crude matter up hoping to get members' ideas, for example, for filtering out artifacts and extreme values, get input on allowing for designed-in mask leaks and much more, including need to address blunders and errors. I see reasonable agreement between breath shortfall "bars" and the majority of FL. However, the indications inside the green box seem strange at the moment. OSCAR indications of drops in MV are mostly concordant with bars indications of shortfall timing.

All said this far, I see substantial agreement between FL and breath shortfall--those FL marks mean something worthwhile. But Resmed factors in wave shape and other indicators to react fast to needs for pressure adjustments up or down. However, to me now, the zoomed approx. 20 minutes are puzzling with fast look looks  at OSCAR's MV, RR and FL vs what little the breath volume bars ("BVB")? indicate by  presence or sparse low volumes.

One thing we need to remember, is that to report FL change upward the RM must see a change, and those increasing RM flag sizes are a representation of breath volume reduction. My approach here, in contrast, purports to measure actual absolute breath reductions. The zoomed significant FL vs the missing and tiny BVB is puzzling. Overall, from other graphing, inspiration time is up after the OA, too. I haven't thought enough about this, but wonder if FL levels indicated (or negligible BVB) are misleading or are actually indicating that there is an underlying continuous flow limitation (as unrounded inspiration-peaks indicate) through most of that period, and, it is not reflected in the variations indicated by FL. (I believe RM are responsive to reduction in flow limitations, too, of course--do not rely on built-in "dead periods of no change to drop pressure and flag size).

A couple of other notes to help interpret what is in attachments:

--The FFM  mask was a RM F-10. 

--The omissions of the breath volume trace, as is most obvious in the zoomed section, can-be/are ambiguous. Those arise from either an inspiration less than 0.025 L or more than 0.47 L, the latter being my base line for measuring and presenting breath shortfall. The traces shown arel in this range .025 L < single inspriation (breath) < 0.47 L, the .. The guide numbers in the graph reflect a scale factor of 100 (to work within Somnopose data requirements and OSCAR import). For now, one can tell from context (other metrics in the view) which applies.

--Once roughest edges of this appproach are smoothed some, then I hope to use a trailing sliding window to adjust for RR, maybe leak. 

As always, I have a lot of blather in mind for comment, but won't go there now. Gotta get that air hose hooked to closed eyes.