5 Messages
Digest #4490
Messages
Mon Feb 25, 2019 3:32 pm (PST) . Posted by:
gz_cp
I found that this RCHGetWebData method did not work if the page return is very large, such as
https://api.census.gov/data/2017/acs/acs5?get=B01003_001E,B01001H_008E,B01001H_009E,B01001H_010E,B01001H_011E,B01001H_023E,B01001H_024E,B01001H_025E,B01001H_026E,B01001D_008E,B01001D_009E,B01001D_010E,B01001D_011E,B01001D_023E,B01001D_024E,B01001D_025E,B01001D_026E,B19013_001E,B19025_001E,B17026_013E,C17002_008E,B23006_023E,NAME&for=zip%20code%20tabulation%20area:* https://api.census.gov/data/2017/acs/acs5?get=B00001_001E,NAME&for=zip%20code%20tabulation%20area:*
For such as page, it needs more then 30000 rows, and Excel becomes weird in that per F9 calcuation only updates one row.
So I need to press F9 30000 times to get all data downloaded.
For state data where there is 50 rows, your example works correctly. One F9 calcuates all values. But for when the rows gets to 30000 for zip code data (the sheet gets large), one F9 only calculates one row.
Is it possible to solve this problem? Maybe a way to increase RCHGetWebData limit beyond 32767 so that we don't need to call that function 30000 times which maybe the cause of the problem?
Thank you.
https://api.census.gov/data/2017/acs/acs5?get=B01003_001E,B01001H_008E,B01001H_009E,B01001H_010E,B01001H_011E,B01001H_023E,B01001H_024E,B01001H_025E,B01001H_026E,B01001D_008E,B01001D_009E,B01001D_010E,B01001D_011E,B01001D_023E,B01001D_024E,B01001D_025E,B01001D_026E,B19013_001E,B19025_001E,B17026_013E,C17002_008E,B23006_023E,NAME&for=zip%20code%20tabulation%20area:* https://api.census.gov/data/2017/acs/acs5?get=B00001_001E,NAME&for=zip%20code%20tabulation%20area:*
For such as page, it needs more then 30000 rows, and Excel becomes weird in that per F9 calcuation only updates one row.
So I need to press F9 30000 times to get all data downloaded.
For state data where there is 50 rows, your example works correctly. One F9 calcuates all values. But for when the rows gets to 30000 for zip code data (the sheet gets large), one F9 only calculates one row.
Is it possible to solve this problem? Maybe a way to increase RCHGetWebData limit beyond 32767 so that we don't need to call that function 30000 times which maybe the cause of the problem?
Thank you.
Mon Feb 25, 2019 5:30 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
That's why you need to use the cascading method I described...it finds each
one, one after the other, so the total length is irrelevant.
The 32767-byte limit is an EXCEL one.
F9 typically is not an effective way to recalculate add-in functions. The
smfForceRecalculation macro should be used. But your F9 behavior sounds
like something that happens when the functions are using unresolved links.
But, quite frankly, if you're grabbing 30000+ rows of data, individual
add-in functions is probably not the way to go. You should write your own
parsing process in VBA. That's why I do functions like
smfGetYahooPortfolioView() or smfGetYahooHistory(). They can be reading
large files and handling them all at one, while using individual add-in
functions in the worksheet for each data item would work, but take a LONG
time. So I parse the file within VBA and then output the array into the
worksheet when the VBA processing is done. MUCH FASTER!
That particular file is over 4 million bytes long. Something as simple as
this processes the file in just a second or so:
Sub Test2()
sURL = "
https://api.census.gov/data/2017/acs/acs5?get=B01003_001E,B01001H_008E,B01001H_009E,B01001H_010E,B01001H_011E,B01001H_023E,B01001H_024E,B01001H_025E,B01001H_026E,B01001D_008E,B01001D_009E,B01001D_010E,B01001D_011E,B01001D_023E,B01001D_024E,B01001D_025E,B01001D_026E,B19013_001E,B19025_001E,B17026_013E,C17002_008E,B23006_023E,NAME&for=zip%20code%20tabulation%20area:*
"
Dim aData As Variant
aData = Split(smfGetWebPage(sURL), "[")
Range("B2").Resize(UBound(aData) + 1) = Application.Transpose(aData)
End Sub
Note that I use the smfGetWebPage() function above instead of
RCHGetWebData(). That's because RCHGetWebData() is a worksheet function,
which handles the worksheet limitation of the 32767-byte string in a cell.
But smfGetWebPage() returns all of the data from the web page. It would
work in a worksheet, until the data being returned is longer than 32767
bytes. Then it would return #VALUE!, because the string is too long to put
into that worksheet cell. That's why I wrote the RCHGetWebData() function
for worksheet use.
Parsing each row into columns would probably require a loop, splitting each
line into a new 2-dimensional array to hold the results.
On Mon, Feb 25, 2019 at 4:32 PM gz_cp@... wrote:
>
> I found that this RCHGetWebData method did not work if the page return is
> very large, such as
>
>
> https://api.census.gov/data/2017/acs/acs5?get=B01003_001E,B01001H_008E,B01001H_009E,B01001H_010E,B01001H_011E,B01001H_023E,B01001H_024E,B01001H_025E,B01001H_026E,B01001D_008E,B01001D_009E,B01001D_010E,B01001D_011E,B01001D_023E,B01001D_024E,B01001D_025E,B01001D_026E,B19013_001E,B19025_001E,B17026_013E,C17002_008E,B23006_023E,NAME&for=zip%20code%20tabulation%20area:*
> <https://api.census.gov/data/2017/acs/acs5?get=B00001_001E,NAME&for=zip%20code%20tabulation%20area:*>
> For such as page, it needs more then 30000 rows, and Excel becomes weird
> in that per F9 calcuation only updates one row.
> So I need to press F9 30000 times to get all data downloaded.
>
> For state data where there is 50 rows, your example works correctly. One
> F9 calcuates all values. But for when the rows gets to 30000 for zip code
> data (the sheet gets large), one F9 only calculates one row.
>
> Is it possible to solve this problem? Maybe a way to increase
> RCHGetWebData limit beyond 32767 so that we don't need to call that
> function 30000 times which maybe the cause of the problem?
>
>
one, one after the other, so the total length is irrelevant.
The 32767-byte limit is an EXCEL one.
F9 typically is not an effective way to recalculate add-in functions. The
smfForceRecalculation macro should be used. But your F9 behavior sounds
like something that happens when the functions are using unresolved links.
But, quite frankly, if you're grabbing 30000+ rows of data, individual
add-in functions is probably not the way to go. You should write your own
parsing process in VBA. That's why I do functions like
smfGetYahooPortfolioView() or smfGetYahooHistory(). They can be reading
large files and handling them all at one, while using individual add-in
functions in the worksheet for each data item would work, but take a LONG
time. So I parse the file within VBA and then output the array into the
worksheet when the VBA processing is done. MUCH FASTER!
That particular file is over 4 million bytes long. Something as simple as
this processes the file in just a second or so:
Sub Test2()
sURL = "
https://api.census.gov/data/2017/acs/acs5?get=B01003_001E,B01001H_008E,B01001H_009E,B01001H_010E,B01001H_011E,B01001H_023E,B01001H_024E,B01001H_025E,B01001H_026E,B01001D_008E,B01001D_009E,B01001D_010E,B01001D_011E,B01001D_023E,B01001D_024E,B01001D_025E,B01001D_026E,B19013_001E,B19025_001E,B17026_013E,C17002_008E,B23006_023E,NAME&for=zip%20code%20tabulation%20area:*
"
Dim aData As Variant
aData = Split(smfGetWebPage(sURL), "[")
Range("B2").Resize(UBound(aData) + 1) = Application.Transpose(aData)
End Sub
Note that I use the smfGetWebPage() function above instead of
RCHGetWebData(). That's because RCHGetWebData() is a worksheet function,
which handles the worksheet limitation of the 32767-byte string in a cell.
But smfGetWebPage() returns all of the data from the web page. It would
work in a worksheet, until the data being returned is longer than 32767
bytes. Then it would return #VALUE!, because the string is too long to put
into that worksheet cell. That's why I wrote the RCHGetWebData() function
for worksheet use.
Parsing each row into columns would probably require a loop, splitting each
line into a new 2-dimensional array to hold the results.
On Mon, Feb 25, 2019 at 4:32 PM gz_cp@... wrote:
>
> I found that this RCHGetWebData method did not work if the page return is
> very large, such as
>
>
> https://api.census.gov/data/2017/acs/acs5?get=B01003_001E,B01001H_008E,B01001H_009E,B01001H_010E,B01001H_011E,B01001H_023E,B01001H_024E,B01001H_025E,B01001H_026E,B01001D_008E,B01001D_009E,B01001D_010E,B01001D_011E,B01001D_023E,B01001D_024E,B01001D_025E,B01001D_026E,B19013_001E,B19025_001E,B17026_013E,C17002_008E,B23006_023E,NAME&for=zip%20code%20tabulation%20area:*
> <https://api.census.gov/data/2017/acs/acs5?get=B00001_001E,NAME&for=zip%20code%20tabulation%20area:*>
> For such as page, it needs more then 30000 rows, and Excel becomes weird
> in that per F9 calcuation only updates one row.
> So I need to press F9 30000 times to get all data downloaded.
>
> For state data where there is 50 rows, your example works correctly. One
> F9 calcuates all values. But for when the rows gets to 30000 for zip code
> data (the sheet gets large), one F9 only calculates one row.
>
> Is it possible to solve this problem? Maybe a way to increase
> RCHGetWebData limit beyond 32767 so that we don't need to call that
> function 30000 times which maybe the cause of the problem?
>
>
Mon Feb 25, 2019 9:45 pm (PST) . Posted by:
gz_cp
Thanks again for your great replies and functions.
Mon Feb 25, 2019 11:18 pm (PST) . Posted by:
gz_cp
Does smfGetWebPage() load from the cache or always download the latest content from the web? If it's the former, how do we refresh it to ensure that we get the most updated web contents?
Thanks.
Thanks.
Tue Feb 26, 2019 3:31 am (PST) . Posted by:
"Higrm" higrm
Simply amazing.
Thank you for providing such versatile code.
On Tuesday, February 26, 2019, 2:31:26 AM GMT+1, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
That's why you need to use the cascading method I described...it finds each one, one after the other, so the total length is irrelevant.
The 32767-byte limit is an EXCEL one.
F9 typically is not an effective way to recalculate add-in functions. The smfForceRecalculation macro should be used. But your F9 behavior sounds like something that happens when the functions are using unresolved links.
But, quite frankly, if you're grabbing 30000+ rows of data, individual add-in functions is probably not the way to go. You should write your own parsing process in VBA. That's why I do functions like smfGetYahooPortfolioView() or smfGetYahooHistory().. They can be reading large files and handling them all at one, while using individual add-in functions in the worksheet for each data item would work, but take a LONG time. So I parse the file within VBA and then output the array into the worksheet when the VBA processing is done. MUCH FASTER!
That particular file is over 4 million bytes long. Something as simple as this processes the file in just a second or so:
Sub Test2() sURL = "https://api.census.gov/data/2017/acs/acs5?get=B01003_001E,B01001H_008E,B01001H_009E,B01001H_010E,B01001H_011E,B01001H_023E,B01001H_024E,B01001H_025E,B01001H_026E,B01001D_008E,B01001D_009E,B01001D_010E,B01001D_011E,B01001D_023E,B01001D_024E,B01001D_025E,B01001D_026E,B19013_001E,B19025_001E,B17026_013E,C17002_008E,B23006_023E,NAME&for=zip%20code%20tabulation%20area:*"
Dim aData As Variant
aData = Split(smfGetWebPage(sURL), "[") Range("B2").Resize(UBound(aData) + 1) = Application.Transpose(aData) End Sub
Note that I use the smfGetWebPage() function above instead of RCHGetWebData(). That's because RCHGetWebData() is a worksheet function, which handles the worksheet limitation of the 32767-byte string in a cell. But smfGetWebPage() returns all of the data from the web page. It would work in a worksheet, until the data being returned is longer than 32767 bytes. Then it would return #VALUE!, because the string is too long to put into that worksheet cell. That's why I wrote the RCHGetWebData() function for worksheet use.
Parsing each row into columns would probably require a loop, splitting each line into a new 2-dimensional array to hold the results.
On Mon, Feb 25, 2019 at 4:32 PM gz_cp@... wrote:
I found that this RCHGetWebData method did not work if the page return is very large, such as
| https://api.census.gov/data/2017/acs/acs5?get=B01003_001E,B01001H_008E,B01001H_009E,B01001H_010E,B01001H_011E,B01001H_023E,B01001H_024E,B01001H_025E,B01001H_026E,B01001D_008E,B01001D_009E,B01001D_010E,B01001D_011E,B01001D_023E,B01001D_024E,B01001D_025E,B01001D_026E,B19013_001E,B19025_001E,B17026_013E,C17002_008E,B23006_023E,NAME&for=zip%20code%20tabulation%20area:* |
For such as page, it needs more then 30000 rows, and Excel becomes weird in that per F9 calcuation only updates one row.So I need to press F9 30000 times to get all data downloaded.
For state data where there is 50 rows, your example works correctly. One F9 calcuates all values.. But for when the rows gets to 30000 for zip code data (the sheet gets large), one F9 only calculates one row.
Is it possible to solve this problem? Maybe a way to increase RCHGetWebData limit beyond 32767 so that we don't need to call that function 30000 times which maybe the cause of the problem?
Thank you for providing such versatile code.
On Tuesday, February 26, 2019, 2:31:26 AM GMT+1, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
That's why you need to use the cascading method I described...it finds each one, one after the other, so the total length is irrelevant.
The 32767-byte limit is an EXCEL one.
F9 typically is not an effective way to recalculate add-in functions. The smfForceRecalculation macro should be used. But your F9 behavior sounds like something that happens when the functions are using unresolved links.
But, quite frankly, if you're grabbing 30000+ rows of data, individual add-in functions is probably not the way to go. You should write your own parsing process in VBA. That's why I do functions like smfGetYahooPortfolioView() or smfGetYahooHistory().. They can be reading large files and handling them all at one, while using individual add-in functions in the worksheet for each data item would work, but take a LONG time. So I parse the file within VBA and then output the array into the worksheet when the VBA processing is done. MUCH FASTER!
That particular file is over 4 million bytes long. Something as simple as this processes the file in just a second or so:
Sub Test2() sURL = "https://api.census.gov/data/2017/acs/acs5?get=B01003_001E,B01001H_008E,B01001H_009E,B01001H_010E,B01001H_011E,B01001H_023E,B01001H_024E,B01001H_025E,B01001H_026E,B01001D_008E,B01001D_009E,B01001D_010E,B01001D_011E,B01001D_023E,B01001D_024E,B01001D_025E,B01001D_026E,B19013_001E,B19025_001E,B17026_013E,C17002_008E,B23006_023E,NAME&for=zip%20code%20tabulation%20area:*"
Dim aData As Variant
aData = Split(smfGetWebPage(sURL), "[") Range("B2").Resize(UBound(aData) + 1) = Application.Transpose(aData) End Sub
Note that I use the smfGetWebPage() function above instead of RCHGetWebData(). That's because RCHGetWebData() is a worksheet function, which handles the worksheet limitation of the 32767-byte string in a cell. But smfGetWebPage() returns all of the data from the web page. It would work in a worksheet, until the data being returned is longer than 32767 bytes. Then it would return #VALUE!, because the string is too long to put into that worksheet cell. That's why I wrote the RCHGetWebData() function for worksheet use.
Parsing each row into columns would probably require a loop, splitting each line into a new 2-dimensional array to hold the results.
On Mon, Feb 25, 2019 at 4:32 PM gz_cp@... wrote:
I found that this RCHGetWebData method did not work if the page return is very large, such as
| https://api.census.gov/data/2017/acs/acs5?get=B01003_001E,B01001H_008E,B01001H_009E,B01001H_010E,B01001H_011E,B01001H_023E,B01001H_024E,B01001H_025E,B01001H_026E,B01001D_008E,B01001D_009E,B01001D_010E,B01001D_011E,B01001D_023E,B01001D_024E,B01001D_025E,B01001D_026E,B19013_001E,B19025_001E,B17026_013E,C17002_008E,B23006_023E,NAME&for=zip%20code%20tabulation%20area:* |
For such as page, it needs more then 30000 rows, and Excel becomes weird in that per F9 calcuation only updates one row.So I need to press F9 30000 times to get all data downloaded.
For state data where there is 50 rows, your example works correctly. One F9 calcuates all values.. But for when the rows gets to 30000 for zip code data (the sheet gets large), one F9 only calculates one row.
Is it possible to solve this problem? Maybe a way to increase RCHGetWebDa
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar