Tediously adding data to a table year-over-year, looking for pivot ideas..

[360]

I'm being tasked to provide a list of properties that a company owns, changing year over year, in tabular form. I have to source this info from the company's 10Ks, so its not all in one location and requires quite a bit of having to copy and paste the data (State and Property Name) into Excel for formatting before being able to add it to the table year over year. My manager wants to see it by year at the top (year of the 10K used), states on the left (the state rows increase as new properties get added but remain if properties are removed to show history over time). In the format he's looking for, with north of 10+ years of data, the table is getting enormous and I have to be extra cautious not to make any mistakes with the formatting (adding rows as necessary, etc). See example of what the table would appear like after adding year-over-year data across 3 years. The letters represent property names.. so by year 3 (2010) it shows the company still owns property A in AL, property B in AL was owned until 2009, property G has been owned since 2009, etc:

	2008	2009	2010
AL	A	A	A
AL	B	B
AK	C	C	C
AZ		G	G
CA	D	D	D
CA	E	E	E
FL	F	F	F
GA			H

I'd love to be able to paste the data I need into 3 columns (YEAR, STATE, PROPERTY) and then just pivot the entire set to show the list of properties over time but obv this isn't a pivot table's purpose (showing text in the values area).. and without macros I'm not sure there's a way to do this. I tried using PowerQuery but it displays errors for states with more than two properties using the "Do Not Aggregate" option. I feel as though there's a much easier way of accomplishing this without having to manually manicure the entire sheet to fit new yearly data each time. Anyone have any ideas?

[Keebellah]

You could try a table and filter

[360]

Thanks, unfortunately it can only be in the exact format above.

[Keebellah]

Do you hava a sample file you could attach? Dummy data

[360]

Sure, pls see attached. I've added a 'Steps' tab and dummy sheets to show you the evolution from year to year regarding information from the 10-K.

In actuality, this file would only have the one 'Hospital List' tab and a separate tab on which I'd paste the data from the online 10-K and format it to fit the sheet.

This is how the manager wants to see the data, so he can get a sense of how the company's properties changed from 10-K to subsequent 10-K, being sure to keep properties no longer owned on the spreadsheet so that he can see it on the 'timeline'.

I just find the formatting of new info to fit the sheet a bit unwieldy and time-consuming.. but if there's no easier way to manage it, all good. Just wish there was a pivot function for this kind of text data so could I put all data across all 10-Ks onto one data sheet (3 columns: YEAR, STATE, PROPERTY) then pivot the results to display as per the example.

[Keebellah]

Okay, I think I understand what you are asking and what the wanted output needs to be.
This must be done, and that is my own opinion, with macros which I think I can realize.
If I understand you correctly you will get your data as a one-column list that starts with the State name and tne below that all the Hospitals for that state then the next state etc?

Can I assume that every 10-K you place the list in a 'source sheet'and then want the macro to split it up like your shown sample ADDED sheets?

[Keebellah]

Since you have't answered I am assuming you are busy copying and pasting your tedious data
Okay, I managed to work out a sample file.
First, your manager may require the output like you said and it looks like that but there is a hidden Column B, which you may unhide but it will be hidden after processing unless you disable that line of code.
The reason I did this is to solve the sorting of the results list after a new state has been added or an Item (as I called it in my code)

There is one macro you need to run: ProcessOne()

It will prompt you for a year value which by default is the current year - 1 since I have made the assumption that you run this list at the beginning of every new year

The Worksheets with the Source List are expected to be named yyyy 10K-Data

The Macro will prompt for a year and the worksheet will be used that has that name: e.g. 2001 10-K-Data (Case sensitive!!!)

The code does not contain an error check to see if the worksheet exists so you will have to fine-tune that.

In the Source List ONLY THE STATE NAMES MUST BE BOLD FONT this is use to check if the value is a state or an establishment and the grouping of establishment per state is your responsibility.

Run the macro three times, 2001, 2002 and 2003 to see the results.

Let me know how it goes

[360]

My apologies for the delay in response, I've been out of pocket traveling for a conference.. haha, and yes, also finishing up this tedious manual job of copy and pasting which has almost certainly yielded errors I'll need to check on given all the cutting, pasting and inserting I've had to do. I had a feeling you'd say (in your opinion) that macros would be needed to accomplish this.. mainly bc it's the one area, 'final frontier' of excel so-to-speak that I haven't explored to-date (in terms of programming, not basic utilization).. but more and more I'm coming to the realization that I'll need to dive in and learn it asap. The data I extract for the property list by state comes from clunky tables within the 10-K itself, not so unlike these banded tables within the most recent Target Corp 10-K:

https://www.sec.gov/Archives/edgar/d...707225E26AE563

I'm talking about literally:
- copying and pasting these tables into a separate sheet in excel
- clearing out the formatting
- deleting the other columns that do not contain the state/property info (thankfully the state/property info is all in one column across all 10-Ks)
- using a filter to clear out blanks/spaces, redundant terms/anchors leaving me with just the states followed by their properties underneath
- bolding the state names (thankfully the states are in alphabetical order, unfortunately the properties aren't)
- sorting the property names for each state alphabetically

..THEN the data is ready for the list sheet. Although that entire process sounds tedious in and of itself, its the pasting/inserting/moving data into the 'Hospital List' worksheet which actually takes up the most time because it involves eyeballing and manipulation since the property list by state is required to be in alphabetical order. Once this is done for a given year's 10-K property data, I move on to the next 10-K, find the new year's property data table and paste it over the old data in the aforementioned separate sheet and start the process over again. I do not place every year's formatted 10-K in their own dedicated sheet. There's just one sheet that contains the ever-growing list, and a separate sheet to clean up the data before transferring/adding over to the list.

The macro you wrote works great for the dummy data, I'm going to test this on real-world data and get back to you. As I said, I do not want to keep the formatted 10-K data for each year in their own sheet, I just included them to give you a sense of the data I'm working with, although with regards to how you designed your macros I imagine it would make sense to create dedicated sheets for each year's 10-K, format the data for each one and run the macro for all years in one go. Thanks for your help, will let you know how it goes.

[Keebellah]

I looked at the site (link) but could not really find what you extract to use for this
Could you extract and past the raw data into a worksheet as you do normally do
How does it look, that is what I would like to see, and maybe the macro can do more for you.
My coding is based on the presence of yyyy 10K-Data worksheets, but maybe there is method to retrieve it in another way.

I already extracted the State names so that you would need to make the Sate Bold to be recognized but I will need more info as to how the RAW data looks.

It is no problem to address another file and extract the data from there.

Hope I am making sense.

[360]

Gotcha, indeed you are making sense. Happy to show you the data I'm looking at, once such example is on this 10-K:

https://www.sec.gov/Archives/edgar/d...6e10vk.htm#105

I've attached a spreadsheet with examples in steps of what the raw data looks like when pasted, to what the final version should appear like.. or rather, what I preferred the data to look like so that I could begin the process of transferring the data over to the 'Hospital List' sheet as best I could when I started this project (the 'Hospital' header being redundant of course). I'm really intrigued by how much can be accomplished using macros! Sadly I've never had the need, or so I thought.

Of course, this is just the data from one 10-K for the company being referenced. Their 10-Ks span from 2000-2016 (or 2001-2017 based on release date). The tedious part beyond what I detailed previously has to do with little things the report makers do over time which you have to account for, eg naming a property 'AB Company' one year then 'A.B. Company' the next, etc.. but nothing you can do about it really, just gotta be mindful enough to catch those nuances.

[Keebellah]

Okay, at first sight I could manage it with the raw data (the first sheet)
The establishment's names is another issue but maybe there is way for that too.
Let me think it over. I won't be able to look at this until this evening.
One thing, the dat would be for on particular year, correct?
So you could name this file e.g: "Steps yyyy 10-K Data.xlsx" whre yyyy would be the year?

[360]

Honestly man, for the purposes of this project, I'm perfectly content with conditioning the data myself before utilizing your macro to add the formatted data to the Hospital List. Nevertheless, if you think you can incorporate the raw data cleanup into the macro, it would serve to help me learn for future projects which would be great! I'm unclear on your last question however. The complete Hospital List file would likely be named 'Company XYZ Hospital List' and cover all years of 10-K data incorporated within it, ie using the last example: years 2000-2016. How you'd want the data source tabs to be named outside of the 'Hospital List' tab however, you let me know what works.

[Keebellah]

I have to test a little further but I even managed to extract the data directly from the web page, needs some fine-tuning.
Excel is a tool that can take a lot of work out of your hands.
I hope to be able to attach the working sample tonight.
P.S. You do not need to quote the previous post with your answer(s)

[Keebellah]

Hi, well I managed to put something together and am attaching two files:
1. WorkedOut-Example-V2.1.xlsm
2. 10-K Data.xlsm

Let us see if this does the job for you.
Step 1: Open 10-K Data.xlsm
When you open 10-K Data.xlsm you will see the data from the web link and an opened userform on the right side
Just paste the link of the site where you collect your data and press the Process URL button.
This will refresh / read the site and check if the two reference rows exist, the first with 'Hospital' as only text which indicates the beginning of the list and the last row with the cell containing the text 'Total Licensed Beds at' and will also show the date (the last 4 characters in that cell
CLOSE THE FILE


Step 2: Open WorkedOut-Example-V2.1.xlsm
When you open this one you get the second userform just press begin, it will prompt you to locate and select the 10-K Data.xlsm file and ask to enter the year
Press Begin and it does the job.

Let me know if this does the job.

YOU DO NOT HAVE TO DO ANYTHING LIKE REMOVING ROWS FORMATTING IN THE 10-K Data.xlsm FILE

[360]

Sorry again for the delay, still in transit. This sounds like an incredible feature, I never would've expected such functionality at all from Excel. I'll be back in my office tomorrow morning (EST), I'll be sure to give it a shot first thing in the morning! Thank you so much!

[Keebellah]

Give it a test and if it's successful I would like to show you an idea I have been toying with

[360]

This was a pretty cool concept, I got to play with it this morning but came across some issues then got dragged into meetings. I'm trying it again now from home, it doesn't seem to be able to extract the data for years 2000-2004, however it does work for 2005 which probably means I should've shown you examples of the format for the 10-Ks in earlier years as they're likely structured differently. Unfortunately its hard to gauge how the reports will be structured from year to year (especially earlier years) as the design tends to evolve into something more standard and consistent. The process worked fine for incorporating 2005, but when I tried to enter the 2006 URL into the 10-K Data file, it named it "006".. not a big deal I thought, so I located it using the WorkedOut file but when the macro completed, it looks as though there are instances where a single property is split across different different rows for the same state (eg 'Woodland Community Hospital' in Alabama, no variances in the name itself to merit it being separated). Any thoughts?

Here is the link for the page with all 10-Ks from 2000-2016: https://www.sec.gov/cgi-bin/browse-e...clude&count=40

Ignore the filing date, these reports are always filed the year after but encompass the previous year up to Dec 31st. Within each year's link, the 10-K is the top-most link in the list. You'll notice the 10-K Data file can't identify the starting point for the first 4 years. For the second error I encountered, test on 2005 and 2006:

2005 - https://www.sec.gov/Archives/edgar/d...99757e10vk.htm

2006 - https://www.sec.gov/Archives/edgar/d...05606e10vk.htm

If I did something wrong just let me know. What idea were you toying with, out of curiosity?

[Keebellah]

Let me take a look. I did realize that the filing date that I use to check is in fact the previous year for the output.

In the code I do two searches: (modified it to three in a newer test version)

First one now is the first cell in column A containg "Hospital" as full content no other text this would be the starting row

The last second search (to determine the last row ):
In the code I search for the cell containing the following entry: (only the red part is the string I need)

Total Licensed Beds at December 31, 2008

The year I take form the last four (4) digits which in this case is 2008 so this would be the source to read for 2009 and I took it as 2008

And the last row becomes the row above this one.

The caveat in all this is that if these references do not exist, nothing will happen

I'll look at the links you provided and check my test version which is easier to work with.

I'm sending you a PM for more information.

[AliGW]

@360

Please don't quote whole posts - it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

For normal conversational replies, try using the QUICK REPLY box below.

I have cleaned up some of your posts.

[Keebellah]

The reason 2006 won't work is because the text has an space at the end so the last 4 characters are not 2006 as expected but 2006+ blank and that is 006 + blank.
Corrected that here, will work on it on my test version

[360]

@ali thx, keebellah pointed this out. noted

@keebellah, will try it out

[Keebellah]

Here is the result of my test.
It is all now only in this file.
You can do everything through the userform.
The Userform will appear when you open the file and remain open as long as you do not press the Cancel/Exit button.
If you do, you can reopen it , trigger it, by Right-Ckick mousebutton over Cell A1

If the URL data does not comply, you will be informed.

The reason of why some thing did not work (like 2006) was because the the fields required had a blank after the value, Georgia was not recognized as state because of that so I made sure that all the values are searched as a trimmed value.

You cannot take care of all the user input errors but I think I go the best, the two links 2005 and 2006 are processed correctly.

Good luck. A lot of or your tedious copying and pasting is over for this one

[360]

Can't thank you enough for your help, this has helped tremendously and I look forward to boning up on my macro skills.. I hope you won't mind the odd question or two from me from time to time!

[Keebellah]

No problem, there is always someone around

[Keebellah]

Final update (V2.3) minor corrections (Fine-tuning) and a PDF explanatory document