=WEBSERVICE formula help

redspanna

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,602
Office Version
  1. 365
Platform
  1. Windows
Hi all

I'm simply try to get current portfolio value of a certain Crypto in Cell range D4:D9
By this I mean multiply QTY (column B) of the stock by current value (column C)
However if I enter into column D the simple formula =C4*B4 then I get a #VALUE! error
The formula in Column C is using a =WEBSERVICE formula

Book1
ABCD
1https://cryptoprices.cc/
2
3COINQTYCURRENT PRICEEST.VALUE
4RNDR546421.75 #VALUE!
5KAS2330.04887369
6RFOX1450.01087123
7AZERO2000.808871
8BBANK5656630.01978743
9KUJI2210.688078
Spots
Cell Formulas
RangeFormula
D4D4=C4*B4
C4:C9C4=WEBSERVICE(endpoint&"/"&A4)
Named Ranges
NameRefers ToCells
endpoint=Spots!$A$1C4:C9


so in above example column D should result in following
D4 = 95,623
D5 = 11.37

etc

Appreciate any help
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
i dont have the webservice function as i'm on a mac
But looking at the values - they appear right justified which is text - hence error

what happens if you
=WEBSERVICE(endpoint&"/"&A4) * 1
does that change to a number
or
=Value(WEBSERVICE(endpoint&"/"&A4))
 
Upvote 0
etaf said:
i dont have the webservice function as i'm on a mac
But looking at the values - they appear right justified which is text - hence error

what happens if you
=WEBSERVICE(endpoint&"/"&A4) * 1
does that change to a number
or
=Value(WEBSERVICE(endpoint&"/"&A4))
Click to expand...
Thanks for the reply, however both the given possible solutions still return the #VALUE! error
🤔
 
Upvote 0
redspanna said:
Hi all

I'm simply try to get current portfolio value of a certain Crypto in Cell range D4:D9
By this I mean multiply QTY (column B) of the stock by current value (column C)
However if I enter into column D the simple formula =C4*B4 then I get a #VALUE! error
The formula in Column C is using a =WEBSERVICE formula

Book1
ABCD
1https://cryptoprices.cc/
2
3COINQTYCURRENT PRICEEST.VALUE
4RNDR546421.75 #VALUE!
5KAS2330.04887369
6RFOX1450.01087123
7AZERO2000.808871
8BBANK5656630.01978743
9KUJI2210.688078
Spots
Cell Formulas
RangeFormula
D4D4=C4*B4
C4:C9C4=WEBSERVICE(endpoint&"/"&A4)
Named Ranges
NameRefers ToCells
endpoint=Spots!$A$1C4:C9


so in above example column D should result in following
D4 = 95,623
D5 = 11.37

etc

Appreciate any help
Click to expand...
I have a template that uses the WEBSERVICE function that was working fine until around September 1st when it suddenly would only return a #VALUE error.
HOWEVER, I am on the Insider BETA version, not the release version, although the Web version of Excel is also returning the same error.
The error appears to be generated by the Trust Center - External Connections, however enabling ALL connections does not fix the problem.
Sorry I'm not giving you a solution, but I was interested in what version you're using, although I'm VERY disappointed that the Web version is also returning that error!
 
Upvote 0
Be aware that the sample you provided is creating a URL with a double backslash such as .

I am no fan of VBA, but I was talking to another Excel wiz who is good at VBA, and he gave me what essentially replaces the WEBSERVICE function!
VBA Code: 
Function WebSvc(sURL As String) As String
    Dim XMLHTTP As Object

    Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")

    With XMLHTTP
        .Open "GET", sURL, False
        .send
        WebSvc = .responseText
    End With

    Set XMLHTTP = Nothing
End Function

And it appears to be working!
WebServiceProblem2.xlsm
ABCD
1https://cryptoprices.cc/
2
3COINQTYURLCURRENT PRICE
4RNDR54642https://cryptoprices.cc/RNDR1.87
5KAS233https://cryptoprices.cc/KAS0.04942694
6RFOX145https://cryptoprices.cc/RFOX0.01010508
7AZERO200https://cryptoprices.cc/AZERO0.806098
8BBANK565663https://cryptoprices.cc/BBANK0.02044648
9KUJI221https://cryptoprices.cc/KUJI0.764351
Sheet4
Cell Formulas
RangeFormula
C4:C9C4=$A$1&A4
D4:D9D4=WebSvc(C4)

I have a template that uses WEBSERVICE extensively to grab financial data from the Federal Reserve Economic Data via their API which I use in conjunction with FILTERXML to parse JSON data from the API URL, so relieved it works well there too!

I would MUCH prefer the function worked, but I really wanted to get my template working again, so VBA it is!
 
Upvote 0
jdellasala said:
Be aware that the sample you provided is creating a URL with a double backslash such as .

I am no fan of VBA, but I was talking to another Excel wiz who is good at VBA, and he gave me what essentially replaces the WEBSERVICE function!
VBA Code: 
Function WebSvc(sURL As String) As String
    Dim XMLHTTP As Object

    Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")

    With XMLHTTP
        .Open "GET", sURL, False
        .send
        WebSvc = .responseText
    End With

    Set XMLHTTP = Nothing
End Function

And it appears to be working!
WebServiceProblem2.xlsm
ABCD
1https://cryptoprices.cc/
2
3COINQTYURLCURRENT PRICE
4RNDR54642https://cryptoprices.cc/RNDR1.87
5KAS233https://cryptoprices.cc/KAS0.04942694
6RFOX145https://cryptoprices.cc/RFOX0.01010508
7AZERO200https://cryptoprices.cc/AZERO0.806098
8BBANK565663https://cryptoprices.cc/BBANK0.02044648
9KUJI221https://cryptoprices.cc/KUJI0.764351
Sheet4
Cell Formulas
RangeFormula
C4:C9C4=$A$1&A4
D4:D9D4=WebSvc(C4)

I have a template that uses WEBSERVICE extensively to grab financial data from the Federal Reserve Economic Data via their API which I use in conjunction with FILTERXML to parse JSON data from the API URL, so relieved it works well there too!

I would MUCH prefer the function worked, but I really wanted to get my template working again, so VBA it is!
Click to expand...

Thanks for the help, but when multiplying column D (current price) by column B (quantity) I still get the #VALUE! error 🙄
 
Upvote 0
redspanna said:
Thanks for the help, but when multiplying column D (current price) by column B (quantity) I still get the #VALUE! error 🙄
Click to expand...
I wasn't paying attention! The VBA is returning text. The easy way to fix it is to use NUMBERVALUE:
WebServiceProblem2.xlsm
ABCD
1https://cryptoprices.cc/
2
3COINQTYURLCURRENT PRICE
4RNDR54642https://cryptoprices.cc/RNDR98,902.02
5KAS233https://cryptoprices.cc/KAS11.50
6RFOX145https://cryptoprices.cc/RFOX1.46
7AZERO200https://cryptoprices.cc/AZERO161.13
8BBANK565663https://cryptoprices.cc/BBANK11,565.82
9KUJI221https://cryptoprices.cc/KUJI167.92
Sheet4
Cell Formulas
RangeFormula
C4:C9C4=$A$1&A4
D4:D9D4=NUMBERVALUE(WebSvc(C4))*B4
 
Upvote 0
jdellasala said:
I wasn't paying attention! The VBA is returning text. The easy way to fix it is to use NUMBERVALUE:
WebServiceProblem2.xlsm
ABCD
1https://cryptoprices.cc/
2
3COINQTYURLCURRENT PRICE
4RNDR54642https://cryptoprices.cc/RNDR98,902.02
5KAS233https://cryptoprices.cc/KAS11.50
6RFOX145https://cryptoprices.cc/RFOX1.46
7AZERO200https://cryptoprices.cc/AZERO161.13
8BBANK565663https://cryptoprices.cc/BBANK11,565.82
9KUJI221https://cryptoprices.cc/KUJI167.92
Sheet4
Cell Formulas
RangeFormula
C4:C9C4=$A$1&A4
D4:D9D4=NUMBERVALUE(WebSvc(C4))*B4
Click to expand...


NOPE! still getting #Value! error
here is copy of what I have, complete with your suggestions - you see in column G is the error

Calculator.xlsm
ABCDEFG
1Crypto Prices | Prices for Crypto Currency Coins
2
3BOUGHTCOINQTYAVG BUY PRICECOST (USDT)CURRENT PRICE
424-Sep-23RNDR510.851.56600000799.99https://cryptoprices.cc/RNDR#VALUE!
524-Sep-23KAS14,324.680.04746400679.91https://cryptoprices.cc/KAS
624-Sep-23RFOX55,381.420.00902820499.99https://cryptoprices.cc/RFOX
724-Sep-23AZERO473.10.84548000400.00https://cryptoprices.cc/AZERO
805-Oct-23BBANK13,570.320167490.01764900239.50https://cryptoprices.cc/BBANK
905-Oct-23KUJI491.070.69000000338.84https://cryptoprices.cc/KUJI
10
11
Sheet1
Cell Formulas
RangeFormula
E4:E9E4=C4*D4
F4:F9F4=$B$1&B4
G4G4=NUMBERVALUE(WEBSERVICE(F4))*C4
 
Upvote 0
redspanna said:
NOPE! still getting #Value! error
here is copy of what I have, complete with your suggestions - you see in column G is the error

Calculator.xlsm
ABCDEFG
1Crypto Prices | Prices for Crypto Currency Coins
2
3BOUGHTCOINQTYAVG BUY PRICECOST (USDT)CURRENT PRICE
424-Sep-23RNDR510.851.56600000799.99https://cryptoprices.cc/RNDR#VALUE!
524-Sep-23KAS14,324.680.04746400679.91https://cryptoprices.cc/KAS
624-Sep-23RFOX55,381.420.00902820499.99https://cryptoprices.cc/RFOX
724-Sep-23AZERO473.10.84548000400.00https://cryptoprices.cc/AZERO
805-Oct-23BBANK13,570.320167490.01764900239.50https://cryptoprices.cc/BBANK
905-Oct-23KUJI491.070.69000000338.84https://cryptoprices.cc/KUJI
10
11
Sheet1
Cell Formulas
RangeFormula
E4:E9E4=C4*D4
F4:F9F4=$B$1&B4
G4G4=NUMBERVALUE(WEBSERVICE(F4))*C4
Click to expand...
I believe the issue is with your URL formula in Column F.

Book1
ABCDEFG
1Crypto Prices | Prices for Crypto Currency Coins
2BOUGHTCOINQTYAVG BUY PRICECOST (USDT)CURRENT PRICE
324-Sep-23RNDR510.851.566799.9911https://cryptoprices.cc/RNDR924.639
424-Sep-23KAS14324.680.047464679.9066https://cryptoprices.cc/KAS709.206
524-Sep-23RFOX55381.420.009028499.9945https://cryptoprices.cc/RFOX556.054
624-Sep-23AZERO473.10.84548399.9966https://cryptoprices.cc/AZERO379.08
75-Oct-23BBANK13570.320.017649239.5026https://cryptoprices.cc/BBANK277.465
85-Oct-23KUJI491.070.69338.8383https://cryptoprices.cc/KUJI376.755
Sheet1
Cell Formulas
RangeFormula
E3:E8E3=C3*D3
F3:F8F3="https://cryptoprices.cc/"&B3
G3:G8G3=NUMBERVALUE(WebSvc(F3))*C3
 
Upvote 0
BigBeachBananas said:
I believe the issue is with your URL formula in Column F.

Book1
ABCDEFG
1Crypto Prices | Prices for Crypto Currency Coins
2BOUGHTCOINQTYAVG BUY PRICECOST (USDT)CURRENT PRICE
324-Sep-23RNDR510.851.566799.9911https://cryptoprices.cc/RNDR924.639
424-Sep-23KAS14324.680.047464679.9066https://cryptoprices.cc/KAS709.206
524-Sep-23RFOX55381.420.009028499.9945https://cryptoprices.cc/RFOX556.054
624-Sep-23AZERO473.10.84548399.9966https://cryptoprices.cc/AZERO379.08
75-Oct-23BBANK13570.320.017649239.5026https://cryptoprices.cc/BBANK277.465
85-Oct-23KUJI491.070.69338.8383https://cryptoprices.cc/KUJI376.755
Sheet1
Cell Formulas
RangeFormula
E3:E8E3=C3*D3
F3:F8F3="https://cryptoprices.cc/"&B3
G3:G8G3=NUMBERVALUE(WebSvc(F3))*C3
Click to expand...
Thank you - I'll try that
Appreciate your help
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Search a variable range based on cell address
Replies
4
Views
139
Peter_SSs
Peter_SSs
SaraWitch
  • Solved
Return values based on multiple cells
Replies
4
Views
239
etaf
etaf
E
  • Solved
How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays
Replies
5
Views
264
Fluff
Fluff
A
  • Question
Excel Formula Simplification
Replies
3
Views
318
Peter_SSs
Peter_SSs
S
  • Solved
Blank Rows Causing Errors
Replies
5
Views
239
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,097
Messages
6,123,077
Members
449,094
Latest member
mystic19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top