donkeycrusher
New Member
- Joined
- Aug 13, 2004
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
My apologies group, but I'm not even sure what to call what I'm trying to do. I've exported a bunch of real estate data. There is a set array of data across columns, then a row for each quarter of each submarket, so it's difficult to filter or chart the data the way I need. I have several hundred sub markets, 88 quarters for each sub market, and over 62,000 rows of data. I've pasted a sample of how a portion of the data is arranged for three submarkets go give an idea of the challenge I'm facing. I feel like some advance form of a pivot table or advanced query is what I'm trying to do, but this data isn't organized like any querry or pivot table that i've ever created before.
As an example, I'd like to chart Market Asking Rent for City of Albany, Clifton Park and Columbia for each quarter from 2000 through 2027 to see how they compare against each other. It would be even better if I could apply filters so that I only see submarkets with at least 1000 inventory units and 2022 Q1 rents with Market Effective Rent/SF above $1.80 and a vacancy rate (not shown on this sample) that never fell below 94% between 2008 Q1 and 2011 Q3, then chart the average market sale price per unit for each submarket that meets the test.
As an example, I'd like to chart Market Asking Rent for City of Albany, Clifton Park and Columbia for each quarter from 2000 through 2027 to see how they compare against each other. It would be even better if I could apply filters so that I only see submarkets with at least 1000 inventory units and 2022 Q1 rents with Market Effective Rent/SF above $1.80 and a vacancy rate (not shown on this sample) that never fell below 94% between 2008 Q1 and 2011 Q3, then chart the average market sale price per unit for each submarket that meets the test.
| Period | Geography Name | CBSA Code | Geography Code | Asset Value | Inventory Units | Market Asking Rent Growth | Market Asking Rent Index | Market Asking Rent/SF | Market Asking Rent/Unit | Market Cap Rate | Market Effective Rent Growth | Market Effective Rent/SF | Market Effective Rent/Unit | Market Sale Price Growth | Market Sale Price Index | Market Sale Price Per Unit | Occupancy Rate | Stabilized Vacancy | Vacancy Rate |
| 2008 Q1 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $286,377,696.00 | 1545 | 0.43% | 99.70299 | $1.26 | $1,218.74 | 7.61% | 0.44% | $1.26 | $1,213.09 | -3.81% | 124.5395 | $113,148.04 | 96.52% | 3.47% | 3.48% |
| 2008 Q2 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $269,012,832.00 | 1545 | 0.48% | 100.1791 | $1.27 | $1,224.56 | 7.85% | 0.49% | $1.26 | $1,219.09 | -10.90% | 116.9003 | $106,287.17 | 96.63% | 3.37% | 3.37% |
| 2008 Q3 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $245,145,280.00 | 1545 | 0.22% | 100.4005 | $1.27 | $1,227.27 | 8.23% | 0.24% | $1.27 | $1,221.99 | -17.76% | 108.0843 | $96,857.09 | 96.69% | 3.31% | 3.31% |
| 2008 Q4 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $226,005,440.00 | 1545 | -0.40% | 100 | $1.27 | $1,222.37 | 8.54% | -0.39% | $1.26 | $1,217.26 | -22.66% | 100 | $89,294.92 | 96.65% | 3.35% | 3.35% |
| 2009 Q1 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $213,651,488.00 | 1845 | -0.94% | 99.06254 | $1.26 | $1,210.91 | 8.74% | -2.23% | $1.23 | $1,190.15 | -24.45% | 94.08664 | $84,413.87 | 88.77% | 3.50% | 11.23% |
| 2009 Q2 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $204,650,496.00 | 1845 | -0.93% | 98.1412 | $1.24 | $1,199.65 | 8.87% | -0.80% | $1.22 | $1,180.63 | -22.24% | 90.90114 | $80,857.56 | 91.22% | 3.70% | 8.78% |
| 2009 Q3 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $204,191,072.00 | 1845 | -0.64% | 97.51335 | $1.24 | $1,191.97 | 8.86% | -0.34% | $1.22 | $1,176.59 | -16.50% | 90.24993 | $80,676.04 | 92.88% | 3.82% | 7.12% |
| 2009 Q4 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $209,191,984.00 | 1845 | -0.59% | 96.94279 | $1.23 | $1,185.00 | 8.75% | -0.37% | $1.22 | $1,172.21 | -7.35% | 92.64726 | $82,651.91 | 94.12% | 3.75% | 5.88% |
| 2010 Q1 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $221,067,440.00 | 1845 | -0.39% | 96.56341 | $1.22 | $1,180.36 | 8.55% | -0.16% | $1.21 | $1,170.31 | 4.07% | 97.92011 | $87,343.91 | 95.08% | 3.56% | 4.92% |
| 2010 Q2 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $240,299,648.00 | 1845 | 0.53% | 97.07393 | $1.23 | $1,186.60 | 8.28% | 0.76% | $1.22 | $1,179.15 | 16.13% | 105.5606 | $94,942.57 | 95.88% | 4.12% | 4.12% |
| 2010 Q3 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $261,514,288.00 | 1845 | 0.97% | 98.01321 | $1.24 | $1,198.08 | 8.01% | 1.15% | $1.24 | $1,192.66 | 26.63% | 114.281 | $103,324.49 | 97.26% | 2.73% | 2.74% |
| 2010 Q4 | Albany/Schenectady/Troy - City of Albany | 10580 | G46109 | $280,785,152.00 | 1845 | 0.50% | 98.50604 | $1.25 | $1,204.11 | 7.79% | 0.50% | $1.24 | $1,198.64 | 32.49% | 122.752 | $110,938.42 | 97.53% | 2.47% | 2.47% |
| 2008 Q1 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $526,396,256.00 | 4434 | 0.43% | 99.70308 | $0.98 | $997.89 | 7.47% | 0.44% | $0.98 | $991.46 | -3.75% | 119.1824 | $107,801.81 | 94.55% | 5.44% | 5.45% |
| 2008 Q2 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $494,138,656.00 | 4434 | 0.48% | 100.1798 | $0.99 | $1,002.66 | 7.71% | 0.49% | $0.98 | $996.33 | -10.02% | 112.8362 | $101,195.71 | 94.67% | 5.32% | 5.33% |
| 2008 Q3 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $462,892,320.00 | 4434 | 0.22% | 100.4011 | $0.99 | $1,004.87 | 7.96% | 0.23% | $0.98 | $998.62 | -15.98% | 105.6249 | $94,796.71 | 94.74% | 5.26% | 5.26% |
| 2008 Q4 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $431,591,872.00 | 4434 | -0.40% | 100 | $0.99 | $1,000.86 | 8.20% | -0.40% | $0.98 | $994.64 | -19.20% | 100 | $88,386.62 | 94.68% | 5.31% | 5.32% |
| 2009 Q1 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $420,189,440.00 | 4434 | -0.94% | 99.06155 | $0.98 | $991.46 | 8.23% | -0.98% | $0.97 | $984.88 | -19.93% | 95.43496 | $86,051.50 | 94.52% | 5.47% | 5.48% |
| 2009 Q2 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $402,876,960.00 | 4434 | -0.93% | 98.13885 | $0.97 | $982.23 | 8.36% | -0.95% | $0.96 | $975.55 | -17.44% | 93.15476 | $82,506.03 | 94.30% | 5.70% | 5.70% |
| 2009 Q3 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $401,614,656.00 | 4434 | -0.64% | 97.51022 | $0.96 | $975.94 | 8.36% | -0.65% | $0.96 | $969.24 | -12.45% | 92.4797 | $82,247.52 | 94.16% | 5.84% | 5.84% |
| 2009 Q4 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $411,314,656.00 | 4434 | -0.59% | 96.93894 | $0.96 | $970.22 | 8.25% | -0.58% | $0.95 | $963.59 | -5.17% | 94.82749 | $84,234.01 | 94.24% | 5.75% | 5.76% |
| 2010 Q1 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $433,742,720.00 | 4434 | -0.39% | 96.55889 | $0.95 | $966.42 | 8.07% | -0.37% | $0.95 | $960.02 | 4.92% | 100.1312 | $88,827.10 | 94.46% | 5.54% | 5.54% |
| 2010 Q2 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $471,340,992.00 | 4434 | 0.53% | 97.0697 | $0.96 | $971.53 | 7.81% | 0.57% | $0.95 | $965.46 | 15.79% | 107.8623 | $96,526.93 | 94.79% | 5.20% | 5.21% |
| 2010 Q3 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $512,953,952.00 | 4434 | 0.97% | 98.01003 | $0.97 | $980.94 | 7.56% | 1.00% | $0.96 | $975.12 | 26.18% | 116.6943 | $105,048.93 | 95.17% | 4.82% | 4.83% |
| 2010 Q4 | Albany/Schenectady/Troy - Clifton Park | 10580 | G46110 | $549,853,696.00 | 4434 | 0.50% | 98.50333 | $0.97 | $985.88 | 7.35% | 0.52% | $0.97 | $980.22 | 32.00% | 125.1723 | $112,605.71 | 95.41% | 4.58% | 4.59% |
| 2008 Q1 | Albany/Schenectady/Troy - Columbia County | G52482 | $77,364,656.00 | 911 | 0.42% | 99.7052 | $1.01 | $1,117.76 | 9.18% | 0.72% | $1.00 | $1,106.87 | -4.83% | 120.0451 | $74,893.18 | 90.83% | 9.18% | 9.17% | |
| 2008 Q2 | Albany/Schenectady/Troy - Columbia County | G52482 | $73,055,912.00 | 911 | 0.47% | 100.1766 | $1.01 | $1,123.04 | 9.43% | 0.79% | $1.01 | $1,115.66 | -10.59% | 113.7937 | $70,722.09 | 90.77% | 9.24% | 9.23% | |
| 2008 Q3 | Albany/Schenectady/Troy - Columbia County | G52482 | $68,426,848.00 | 911 | 0.22% | 100.396 | $1.02 | $1,125.50 | 9.73% | 0.52% | $1.01 | $1,121.49 | -15.99% | 106.7621 | $66,240.90 | 90.73% | 9.28% | 9.27% | |
| 2008 Q4 | Albany/Schenectady/Troy - Columbia County | G52482 | $63,841,500.00 | 911 | -0.39% | 100 | $1.01 | $1,121.06 | 10.01% | -1.85% | $0.99 | $1,100.75 | -19.81% | 100 | $61,802.03 | 90.70% | 9.30% | 9.30% | |
| 2009 Q1 | Albany/Schenectady/Troy - Columbia County | G52482 | $60,051,148.00 | 911 | -0.93% | 99.07499 | $1.00 | $1,110.69 | 10.26% | -0.82% | $0.98 | $1,091.75 | -21.38% | 94.38293 | $58,132.77 | 90.69% | 9.31% | 9.31% | |
| 2009 Q2 | Albany/Schenectady/Troy - Columbia County | G52482 | $57,624,132.00 | 911 | -0.91% | 98.17137 | $0.99 | $1,100.56 | 10.42% | -0.58% | $0.98 | $1,085.39 | -19.96% | 91.07556 | $55,783.28 | 90.67% | 9.34% | 9.33% | |
| 2009 Q3 | Albany/Schenectady/Troy - Columbia County | G52482 | $57,480,784.00 | 911 | -0.63% | 97.55567 | $0.99 | $1,093.66 | 10.41% | -0.40% | $0.98 | $1,081.09 | -15.22% | 90.51343 | $55,644.51 | 90.63% | 9.38% | 9.37% | |
| 2009 Q4 | Albany/Schenectady/Troy - Columbia County | G52482 | $58,970,052.00 | 911 | -0.57% | 96.99653 | $0.98 | $1,087.39 | 10.27% | -0.35% | $0.97 | $1,077.29 | -7.12% | 92.88363 | $57,086.21 | 90.65% | 9.36% | 9.35% | |
| 2010 Q1 | Albany/Schenectady/Troy - Columbia County | G52482 | $62,182,496.00 | 911 | -0.39% | 96.62305 | $0.98 | $1,083.20 | 10.04% | -0.19% | $0.97 | $1,075.28 | 4.07% | 98.22821 | $60,196.03 | 90.82% | 9.20% | 9.18% | |
| 2010 Q2 | Albany/Schenectady/Troy - Columbia County | G52482 | $67,759,448.00 | 911 | 0.52% | 97.12127 | $0.98 | $1,088.79 | 9.70% | 0.67% | $0.98 | $1,082.53 | 16.23% | 105.8541 | $65,594.82 | 91.01% | 9.00% | 8.99% | |
| 2010 Q3 | Albany/Schenectady/Troy - Columbia County | G52482 | $73,636,184.00 | 911 | 0.94% | 98.03613 | $0.99 | $1,099.04 | 9.39% | 0.93% | $0.99 | $1,092.58 | 26.52% | 114.5219 | $71,283.82 | 91.25% | 8.77% | 8.75% | |
| 2010 Q4 | Albany/Schenectady/Troy - Columbia County | G52482 | $78,852,376.00 | 911 | 0.49% | 98.52093 | $1.00 | $1,104.48 | 9.13% | 0.50% | $0.99 | $1,097.99 | 32.06% | 122.6657 | $76,333.37 | 91.53% | 8.49% | 8.47% | |
| 2008 Q1 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $622,695,616.00 | 5845 | 0.43% | 99.67051 | $1.24 | $994.99 | 8.24% | 0.44% | $1.24 | $988.61 | -5.17% | 121.9036 | $82,421.65 | 94.75% | 5.25% | 5.25% |
| 2008 Q2 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $583,974,784.00 | 5845 | 0.48% | 100.15 | $1.25 | $999.78 | 8.49% | 0.49% | $1.24 | $993.50 | -11.17% | 115.1386 | $77,296.47 | 94.80% | 5.20% | 5.20% |
| 2008 Q3 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $542,193,472.00 | 5845 | 0.23% | 100.379 | $1.25 | $1,002.07 | 8.82% | 0.25% | $1.25 | $995.94 | -17.00% | 107.2471 | $71,766.18 | 94.87% | 5.13% | 5.13% |
| 2008 Q4 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $502,829,472.00 | 5845 | -0.38% | 100 | $1.25 | $998.28 | 9.12% | -0.36% | $1.24 | $992.32 | -21.14% | 100 | $66,555.85 | 94.90% | 5.10% | 5.10% |
| 2009 Q1 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $473,897,664.00 | 5845 | -0.89% | 99.10535 | $1.24 | $989.35 | 9.34% | -0.97% | $1.23 | $982.66 | -23.62% | 93.11037 | $62,726.36 | 94.86% | 5.14% | 5.14% |
| 2009 Q2 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $437,545,472.00 | 5851 | -0.87% | 98.23913 | $1.23 | $980.70 | 9.56% | -0.88% | $1.22 | $974.04 | -22.93% | 88.73822 | $57,914.69 | 94.77% | 5.23% | 5.23% |
| 2009 Q3 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $436,419,936.00 | 5851 | -0.59% | 97.6574 | $1.22 | $974.90 | 9.56% | -0.59% | $1.21 | $968.33 | -18.91% | 86.96713 | $57,765.71 | 94.71% | 5.29% | 5.29% |
| 2009 Q4 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $446,996,288.00 | 5851 | -0.54% | 97.13196 | $1.21 | $969.65 | 9.44% | -0.53% | $1.20 | $963.15 | -10.81% | 89.19487 | $59,165.62 | 94.72% | 5.28% | 5.28% |
| 2010 Q1 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $471,382,976.00 | 5851 | -0.35% | 96.79206 | $1.21 | $966.26 | 9.23% | -0.33% | $1.20 | $960.00 | 1.03% | 94.06736 | $62,393.51 | 94.85% | 5.15% | 5.15% |
| 2010 Q2 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $510,429,312.00 | 5851 | 0.53% | 97.30875 | $1.22 | $971.42 | 8.95% | 0.57% | $1.21 | $965.52 | 13.84% | 101.0176 | $67,561.79 | 95.10% | 4.90% | 4.90% |
| 2010 Q3 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $552,564,608.00 | 5851 | 0.96% | 98.23806 | $1.23 | $980.69 | 8.67% | 0.99% | $1.22 | $975.08 | 25.28% | 108.952 | $73,138.93 | 95.39% | 4.61% | 4.61% |
| 2010 Q4 | Albany/Schenectady/Troy - Downtown Albany | 10580 | G52200 | $591,900,672.00 | 5851 | 0.51% | 98.73859 | $1.23 | $985.69 | 8.44% | 0.53% | $1.23 | $980.21 | 30.75% | 116.6215 | $78,345.56 | 95.60% | 4.40% | 4.40% |
| 2008 Q1 | Albany/Schenectady/Troy - Fulton County | G67097 | $41,755,216.00 | 689 | 0.43% | 99.70311 | $0.62 | $631.67 | 8.84% | 0.42% | $0.61 | $626.33 | -3.63% | 120.4828 | $60,602.63 | 89.72% | 10.28% | 10.28% | |
| 2008 Q2 | Albany/Schenectady/Troy - Fulton County | G67097 | $39,372,088.00 | 689 | 0.48% | 100.1804 | $0.62 | $634.69 | 9.10% | 0.48% | $0.62 | $629.32 | -10.02% | 114.0339 | $57,143.81 | 89.69% | 10.31% | 10.31% | |
| 2008 Q3 | Albany/Schenectady/Troy - Fulton County | G67097 | $36,797,156.00 | 689 | 0.22% | 100.4021 | $0.62 | $636.10 | 9.40% | 0.22% | $0.62 | $630.71 | -15.94% | 106.8539 | $53,406.61 | 89.70% | 10.30% | 10.30% | |
| 2008 Q4 | Albany/Schenectady/Troy - Fulton County | G67097 | $34,330,272.00 | 689 | -0.40% | 100 | $0.62 | $633.55 | 9.68% | -0.40% | $0.62 | $628.19 | -20.11% | 100 | $49,826.23 | 89.72% | 10.28% | 10.28% | |
| 2009 Q1 | Albany/Schenectady/Troy - Fulton County | G67097 | $32,284,302.00 | 689 | -0.93% | 99.06986 | $0.61 | $627.65 | 9.92% | -0.93% | $0.61 | $622.37 | -21.71% | 94.32062 | $46,856.75 | 89.74% | 10.26% | 10.26% | |
| 2009 Q2 | Albany/Schenectady/Troy - Fulton County | G67097 | $30,924,010.00 | 689 | -0.92% | 98.15977 | $0.61 | $621.89 | 10.09% | -0.92% | $0.60 | $616.62 | -20.31% | 90.86905 | $44,882.45 | 89.73% | 10.26% | 10.27% | |
| 2009 Q3 | Albany/Schenectady/Troy - Fulton County | G67097 | $30,760,948.00 | 689 | -0.63% | 97.5378 | $0.60 | $617.95 | 10.10% | -0.63% | $0.60 | $612.73 | -15.70% | 90.07315 | $44,645.79 | 89.71% | 10.28% | 10.29% | |
| 2009 Q4 | Albany/Schenectady/Troy - Fulton County | G67097 | $31,461,250.00 | 689 | -0.58% | 96.97346 | $0.60 | $614.37 | 9.98% | -0.58% | $0.60 | $609.18 | -6.89% | 93.10507 | $45,662.19 | 89.68% | 10.31% | 10.32% | |
| 2010 Q1 | Albany/Schenectady/Troy - Fulton County | G67097 | $34,059,372.00 | 689 | -0.39% | 96.59639 | $0.60 | $611.98 | 9.70% | -0.39% | $0.59 | $606.80 | 4.48% | 98.54304 | $49,433.05 | 89.66% | 10.34% | 10.34% | |
| 2010 Q2 | Albany/Schenectady/Troy - Fulton County | G67097 | $36,384,444.00 | 689 | 0.52% | 97.09685 | $0.60 | $615.15 | 9.43% | 0.52% | $0.60 | $609.95 | 17.04% | 106.3532 | $52,807.61 | 89.66% | 10.34% | 10.34% | |
| 2010 Q3 | Albany/Schenectady/Troy - Fulton County | G67097 | $39,537,868.00 | 689 | 0.95% | 98.02322 | $0.61 | $621.02 | 9.13% | 0.96% | $0.60 | $615.79 | 26.96% | 114.3567 | $57,384.43 | 89.68% | 10.31% | 10.32% | |
| 2010 Q4 | Albany/Schenectady/Troy - Fulton County | G67097 | $42,335,964.00 | 689 | 0.49% | 98.50489 | $0.61 | $624.08 | 8.88% | 0.49% | $0.61 | $618.83 | 31.56% | 122.4862 | $61,445.52 | 89.74% | 10.25% | 10.26% |
