•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Extracting reports from a website based medical records system, refining the data, and consolidating it in a sheet
Our take
Navigating a web-based medical records system to extract and consolidate reports can seem daunting, especially for those new to Excel. However, with your experience in R and Python, you're well-equipped to tackle this challenge. Start by learning how to automate the login process using Selenium to streamline report generation. Once you've downloaded "Report A1," "Report B1," and "Report C2," focus on transforming and analyzing the data within those files.
Hi, I am hoping someone can point me in the right direction. I know this is a loaded question, but even if you are able to help me understand what features I can use to make some of this happen, I'd really appreciate it! Thank you in advance!
I am fairly new at excel but have some experience with R and python for data analysis. I have thought of a bit of a workflow but I am happy to clarify more!
I need to do the following:
- From a website that hosts a cloud based medical charting system that is behind a username and password, I need to log in, navigate to a certain webpage, have certain buttons and items "clicked" to generate an excel report.
- Download that report. Let's call it "Report A1"
- In a similar fashion, download other reports. Let's call them "Report B1" and "Report C2".
- Open each report, do some data transformation and analysis, and put all the data from report A, B, and C it into a new sheet in a certain format.
I would love to know: What are the steps and systems I need to learn to make this happen? I know this might end up being a long project but I'm willing to learn and spend some time.
Problems I anticipate:
Potential problem #1: Logging in to actually get the data from the website that houses the electronic medical charting system. A while back I was semi successful in writing a python script with selenium if I remember correctly, to 'click' the right things and get report A, but it was tough and finicky. If this isn't possible I can manually download the reports and put them in a folder.
Potential problem #2: The name of the report will change but will always contain certain phrases, such as Report type B will always be named "Report B1" or "Report B2" or "Report B3" etc. So while name of the report may change I can perhaps stick them all in a folder and when importing 'filter' them based on a name.)
I know this is a loaded question, but please point me in the right direction! Thank you!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- What would you do with this task, and how long would it take you to do it?I'm going to describe a situation as specifically as I can. I am curious what people would do in this situation, I worry that I complicate things for myself. I'm describing the whole task as it was described to me and then as I discovered it. Ultimately, I'm here to ask you, what do you do, and how long does it take you to do it? I started a new role this month, I am new to advertising modeling methods like mmm, so I am reading a lot about how to apply the methods specific to mmm in R and python, I use VScode, I don't have a github copilot license, I get to use copilot through windows office license. Although this task did not involve modeling, I do want to ask about that kind of task another day if this goes over well. The task 5, excel sheets are to be provided. You are told that this is a clients data that was given to another party for some other analysis and augmentation. This is a quality assurance task. The previous process was as follows; the data the data structure: 1 workbook per industry for 5 industries 4 workbooks had 1 tab, 1 workbook had 3 tabs each tab had a table that had a date column in days, 2 categorical columns advertising_partner, line_of_business and at least 2 numeric columns per work book. some times data is updated from our side and the partner has to redownload the data and reprocess and share again the process this is done once per client, per quarter (but it's just this client for now) open each workbook navigate to each tab the data is in a "controllable" table bing bing home home impressions spend partner dropdown line of business dropdown where bing and home are controlled with drop down toggles, with a combination of 3-4 categories each. compare with data that is to be downloaded from a tableau dashboard end state: the comparison of the metrics in tableau to the excel tables to ensure that "the numbers are the same" the categories presented map 1 to 1 with the data you have downloaded from tableau aggregate the data in a pivot table, select the matching categories, make sure the values match additional info about the file the summary table is a complicated sumproduct look up table against an extremely wide table hidden to the left. the summary table can start as early as AK and as late as FE. there are 2 broadly different formats of underlying data in the 5 notebooks, with small structure differences between the group of 3. in the group of 3 the structure of this wide table is similar to the summary table with categories in the column headers describing the metric below it. but with additional categories like region, which is the same value for every column header. 1 of these tables has 1 more header category than the other 2 the left most columns have 1 category each, there are 3 date columns for day, quarter. REGION USA USA USA PARTNER bing bing google LOB home home auto impressions spend ...etc date quarter impressions spend ...etc 2023-01-01 q1 1 2 ...etc 2023-01-02 q1 3 4 ...etc in the group of 2 the left most categories are actually the categorical headers in the group of 3, and the metrics, the values in each category mach the dates are now the headers of this very wide table the header labels are separated from the start of the values by 1 column there is an empty row immediately below the final row for column headers. date Label 2023-01-01 2023-01-02 year 2023 2023 quarter q1 q1 blank row REGION PARTNER LOB measure blank row US bing home impressions 1 3 US bing home spend 2 4 US google auto ...etc ...etc ... etc The question is, what do you do, and how long does it take you to do it? I am being honest here, I wrote out this explaination basically in the order in which I was introduced to the information and how I discovered it. (Oh it's easy if it's all the same format even if it's weird, oh there are 2-ish different formatted files) the meeting of this task ended at 11:00AM. I saw this copy paste manual etl project and I simply didn't want to do it. So I outlined my task by identifying the elements of the table, column name ranges, value ranges, stacked / pivoted column ranges, etc... for an R script to extract that data. by passing the ranges of that content to an argument make_clean_table(left_columns="B4:E4", header_dims=c(..etc)) and functions that extract that convert that excel range into the correct position in the table to extract that element. Then the data was transformed to create a tidy long table. the function gets passed once per notebook extracting the data from each worksheet, building a single table with the columns for the workbook industry, the category in the tab, partner, line of business, spend, impressions, etc... IMO; ideally (if I have to check their data in excel that is), I'd like the partner to redo their report so that I received a workbook with the underlying data in a traditionally tabular form and their reporting page to use power query and table references and not cell ranges and formula. submitted by /u/TheTresStateArea [link] [comments]
- Not sure how to automate counting of monitoring info gained from microsoft form, any guidance?I'd like to preface this with the facts that I'm 1. using excel for the web, and 2. very new to using excel. I work with a group of volunteers, and we have to take in monitoring information from each client we work with (think age ethnicity disability etc). At the end of the year we have to report this info to another institution. For the past few years the volunteers have been manually counting this data which takes around 10+ hours. My idea was to switch things up by having volunteers input data into a microsoft form linked to an excel file. One sheet for raw data, 12 more for each month of the year. My goal is to make it so excel automatically updates the month's count when a new client's data is added. Previously all data has been counted under the name of the volunteer who took it. The final reports look something like this: John Smith Jane Doe Age: 18-25 5 0 25-60 0 4 60+ 0 1 Ethnicity: White 2 1 Black 2 2 Mixed 1 3 Disabled: Yes 3 1 No 2 4 I have the easy part (Microsoft Form linked to Excel) done, but I'm stuck on how to get the information from the raw data 1. into the monthly sheets 2. have this information automatically add up over the year. I'm looking primarily for guidance on how to do this and what functions I should look into. I have made a table of the info in the raw data sheet which I think should help with being able to move it across the sheets. Based on research so far I think COUNTIFs may be something to experiment with? As said I'm a complete newbie, but I want to know how to do this And how the process works rather than just being handed an answer. Happy to provide more info if needed. Any resources/guidance would be really appreciated 🙏 submitted by /u/incrediblycalmwithit [link] [comments]
- Architecture for two separate projects: A Nationwide Template and an Automated 30+ Page ReportI am designing a data solution that needs to scale from a local proof-of-concept to a nationwide implementation. I need advice on the most stable "tool stack" to ensure reliability for non-technical end-users. Technical Proficiency / Skill Set: Power Query: Self-taught, comfortable with ETL and merging multiple data sources. VBA: Basic/Intermediate (primarily used for automation, often AI-assisted). Web/Coding: Very basic HTML/CSS knowledge, but no JavaScript/Python experience. Context: I have a logical workflow mindset, but I am seeking more of the "industry standard" to move away from manual, repetitive processes. Project A: The Nationwide Data Template (Standalone Tool) The Goal: Build a data setup to be implemented nationwide for other court locations. Input: Must process CSV files from a central authority (no direct database access due to high security). Users: Staff at all technical levels. They should only interact with 1-3 specific "result" sheets. The plan is that they will be told only to use one specific folder and of course the sheets will be protected. Constraints: I will have no access to their local folders or machines for troubleshooting. The tool must be "bulletproof" and handle data refreshes without user-side errors. Question: Is Power Query + VBA robust enough for a rollout with zero local support, or should I move the logic to Power Pivot/DAX to reduce the risk of users "breaking" the tool? Project B: The 30+ Page Reporting Nightmare (Internal Workflow) The Goal: Automate a 32-page yearly report that I personally produce. The Problem: Currently, I manually copy/paste tables and charts from Excel into Word. I started with keeping the chains but the document became so heavy it was near-crashing, and because it's so unstable, I've had to break the chains and make the data static. The Requirement: I need a smarter "bridge" between Excel and a formatted report (Word or PDF) to replace the manual "Ctrl+C / Ctrl+V" marathon. Question: What is the industry standard for this? Is this where I move to Power BI, or is there a reliable way to automate Word via Excel objects that won't crash the document? Technical Questions: Stability: For a rollout with zero local support, is a Power Query + VBA combination robust enough? Would moving the logic to Power Pivot/DAX improve stability and reduce "breakability" for end-users? Reporting Pipeline: What is the industry standard for pushing large amounts of dynamic Excel data into a formatted 30-page report? Is Power BI the logical successor here, or is there a reliable way to automate Word via Excel (e.g., Content Controls or specific VBA objects) that won't crash? Future-proofing: Given the need for a national standard, should I prioritize mastering Power BI or deepening Advanced VBA/Data Modeling within Excel to solve these specific deployment issues? Excel Version: Microsoft 365 (Build 17928.20440), Semi-Annual Enterprise Channel. submitted by /u/screaming-Zebra [link] [comments]
- Data Prep for Software TransitionI’ve been tasked with preparing approximately 7,000 lines of membership data for import into our new system. I have a solid understanding of what needs to be done to build the master data sheet for import, but I’m also aware that I don’t have the technical depth to execute every part of this process independently. About the Data: Sheet 1: Base membership data + contact info ~7,000 records, each with a unique System ID (primary identifier) Sheet 2: Member credit balances ~50 records, includes System ID, needs to be merged into Sheet 1 by way of similar System ID Sheet 3: Member debit balances ~100 records, includes System ID, also needs to be merged into Sheet 1 (after Sheet 2 fields) by way of similar System ID Tools / Limitations I’m currently using the Excel web application via SharePoint, and I’m noticing that some features shown in tutorials (like certain tabs or tools) aren’t available to me. I’ve come across concepts like: Left / Inner / Outer joins (via Python/Pandas) Power Query Indexing / lookup methods However, I don’t have coding experience, and I’m unsure whether my current version of Excel supports Power Query or similar functionality. Questions: What is the simplest way for a beginner to merge data into corresponding columns using a shared System ID? Is working with around 7,000 rows (and potentially up to 20,000 if guest data is included) too large to handle using column-by-column lookup methods? Would you recommend cleaning and validating the data before merging the sheets, or is it better to complete the merge first and then handle data cleanup? --- I'll likely have several more questions over the next few days as I work through this process. If anyone is willing to follow along and offer guidance, I would really appreciate the support. The final data import sheet must be completed and ready to share by April 27. Thank you in advance for your help! submitted by /u/Complex_Chemistry807 [link] [comments]