Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by itjockey
at 2024-07-31 07:55:42
ModifyPoint:500 Replies:18 POST_ID:828644USER_ID:11514
Topic:
Microsoft Excel Spreadsheet Software;Microsoft Office Suite;Visual Basic Programming
Hi experts,
Need Help On this Code
Need Help On this Code
Function GetQuote(Symbol As String, Optional GoogleParameter As String = "last") As String ' This is an Excel VBA function which returns data about a stock ' using Google Finance's XML stream ' On December 6, 2012 the Excel call =GetQuote("VTI") returned 72.55 ' =GetQuote("VTI", "volume") returned 68676 ' Since this function returns strings, I've included a String_to_Number function below ' I got the essential ideas from here: ' http://msdn.microsoft.com/en-us/library/aa163921%28office.10%29.aspx ' BUT before you start, read this: ' http://stackoverflow.com/questions/11245733/declaring-early-bound-msxml-object-throws-an-error-in-vba ' Note: Google has threatened to discontinue this feed in the future ' see my GetQuoteYahoo function as an alternative in that case ' but until then the two streams have special features ' see http://www.philadelphia-reflections.com/blog/2392.htm ' "GoogleParameter" is one of the following node names, it defaults to "last": ' "URL" is also valid in case you want to look at the XML stream returned ' '<xml_api_reply version="1"> ' <finance module_id="0" tab_id="0" mobile_row="0" mobile_zipped="1" row="0" section="0"> ' <symbol data="VTI"/> ' <pretty_symbol data="VTI"/> ' <symbol_lookup_url data="/finance?client=ig&q=VTI"/> ' <company data="Vanguard Total Stock Market ETF"/> ' <exchange data="NYSEARCA"/> ' <exchange_timezone data=""/> ' <exchange_utc_offset data=""/> ' <exchange_closing data=""/> ' <divisor data="2"/> ' <currency data="USD"/> ' <last data="72.55"/> ' <high data="72.69"/> ' <low data="72.45"/> ' <volume data="68676"/> ' <avg_volume data=""/> ' <market_cap data="22385.76"/> ' <open data="72.54"/> ' <y_close data="72.60"/> ' <change data="+0.03"/> ' <perc_change data="0.04"/> ' <delay data="0"/> ' <trade_timestamp data="1 minute ago"/> ' <trade_date_utc data="20121206"/> ' <trade_time_utc data="145144"/> ' <current_date_utc data="20121206"/> ' <current_time_utc data="145323"/> ' <symbol_url data="/finance?client=ig&q=VTI"/> ' <chart_url data="/finance/chart?q=NYSEARCA:VTI&tlf=12"/> ' <disclaimer_url data="/help/stock_disclaimer.html"/> ' <ecn_url data=""/> ' <isld_last data="72.55"/> ' <isld_trade_date_utc data="20121206"/> ' <isld_trade_time_utc data="142903"/> ' <brut_last data=""/> ' <brut_trade_date_utc data=""/> ' <brut_trade_time_utc data=""/> ' <daylight_savings data="false"/> ' </finance> '</xml_api_reply> Dim GoogleXMLstream As MSXML2.DOMDocument Dim oChildren As MSXML2.IXMLDOMNodeList Dim oChild As MSXML2.IXMLDOMNode Dim fSuccess As Boolean Dim URL As String On Error GoTo HandleErr ' create the URL that requests the XML stream from Google Finance URL = "http://www.google.com/ig/api?stock=" & Trim(Symbol) ' In case you want to look at the XML If GoogleParameter = "URL" Then GetQuote = URL Exit Function End If ' pull in the XML stream Set GoogleXMLstream = New MSXML2.DOMDocument GoogleXMLstream.async = False ' wait for completion GoogleXMLstream.validateOnParse = False ' do not validate the XML stream fSuccess = GoogleXMLstream.Load(URL) ' pull in the feed If Not fSuccess Then ' quit on failure MsgBox "error loading Google Finance XML stream" Exit Function End If ' iterate through the nodes looking for one with the name in GoogleParameter GetQuote = GoogleParameter & " is not valid for GetQuote function" Set oChildren = GoogleXMLstream.DocumentElement.LastChild.ChildNodes For Each oChild In oChildren If oChild.nodeName = GoogleParameter Then GetQuote = oChild.Attributes.getNamedItem("data").Text Exit Function End If Next oChild' error handlersExitHere: Exit FunctionHandleErr: MsgBox "Error " & Err.Number & ": " & Err.Description Resume ExitHere ResumeEnd FunctionFunction String_to_Number(num_as_string As String) As Double ' The GetQuote function returns string values ' This function converts numbers in string format to double String_to_Number = Val(num_as_string)End Function 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:40:41:42:43:44:45:46:47:48:49:50:51:52:53:54:55:56:57:58:59:60:61:62:63:64:65:66:67:68:69:70:71:72:73:74:75:76:77:78:79:80:81:82:83:84:85:86:87:88:89:90:91:92:93:94:95:96:97:98:99:100:101:102:103:104:105:106:107:108:109:110:111:112:113:114:115:116:117:118:119:120:121:122:123:124:125:126:127:128:129:130:131:
As I open the workbook it shows me latest price but it doesn't refresh, any idea how do I do that?
Thanks
Attachment:Google-Rates.xlsm
Author: itjockey replied at 2024-09-25 02:04:13
thnks
Expert: MartinLiss replied at 2024-09-24 15:01:47
Please check what accuarcy you want first, on Excel VBA is least at at 1 minute that
is what you want ? how about in 1 hour or 1 day accuracy ? Hope you get my comment.
And state it clearly what you want to this thread, and try to make a test by yourself
from people suggestion first.
I am leaving office, talk you tomorrow if need
Duncan
is what you want ? how about in 1 hour or 1 day accuracy ? Hope you get my comment.
And state it clearly what you want to this thread, and try to make a test by yourself
from people suggestion first.
I am leaving office, talk you tomorrow if need
Duncan
Expert: MartinLiss replied at 2024-09-24 15:01:47
Please check what accuarcy you want first, on Excel VBA is least at at 1 minute that
is what you want ? how about in 1 hour or 1 day accuracy ? Hope you get my comment.
And state it clearly what you want to this thread, and try to make a test by yourself
from people suggestion first.
I am leaving office, talk you tomorrow if need
Duncan
is what you want ? how about in 1 hour or 1 day accuracy ? Hope you get my comment.
And state it clearly what you want to this thread, and try to make a test by yourself
from people suggestion first.
I am leaving office, talk you tomorrow if need
Duncan
Expert: ModernMatt replied at 2024-09-24 09:30:18
itjockey,
Our attempts to attract more activity to your question have not been successful. I recommend you delete and re-post this question, to bring it back to the top of the list in front of the Experts.
HOWEVER, if you are to succeed in obtaining a response, you must also remember that the Experts here are not about to do your programming project for you. It would be very helpful if you got involved and explained your code in more detail and/or worked with the Experts to make the improvements, rather than assuming they will make it all and give you back working code. They are mostly here to help troubleshoot your code with you, which is why we struggle to get Experts to participate when they are simply asked to write the code for you. Some will, but many do not.
I would suggest taking that approach on re-posting the question, or considering hiring someone external to write the code for you if you are not comfortable with doing so yourself.
ModernMatt
Experts Exchange Moderator
Our attempts to attract more activity to your question have not been successful. I recommend you delete and re-post this question, to bring it back to the top of the list in front of the Experts.
HOWEVER, if you are to succeed in obtaining a response, you must also remember that the Experts here are not about to do your programming project for you. It would be very helpful if you got involved and explained your code in more detail and/or worked with the Experts to make the improvements, rather than assuming they will make it all and give you back working code. They are mostly here to help troubleshoot your code with you, which is why we struggle to get Experts to participate when they are simply asked to write the code for you. Some will, but many do not.
I would suggest taking that approach on re-posting the question, or considering hiring someone external to write the code for you if you are not comfortable with doing so yourself.
ModernMatt
Experts Exchange Moderator
Expert: Netminder replied at 2024-09-18 00:32:24
itjockey,
A message has been sent to some additional experts asking them to review your question. We will check back again to see if you are getting the help you need.
Please do not respond to this comment; we will be monitoring your question for activity from the Experts.
Netminder
Senior Admin
A message has been sent to some additional experts asking them to review your question. We will check back again to see if you are getting the help you need.
Please do not respond to this comment; we will be monitoring your question for activity from the Experts.
Netminder
Senior Admin
Expert: Netminder replied at 2024-09-18 00:32:14
itjockey,
I changed the Topic Areas for this question from
--Microsoft Excel Spreadsheet Software
--Microsoft Office Suite
--Microsoft Applications
to
--Microsoft Excel Spreadsheet Software
--Microsoft Office Suite
--Visual Basic Programming
Netminder
Senior Admin
http://www.experts-exchange.com/R_23308.html
I changed the Topic Areas for this question from
--Microsoft Excel Spreadsheet Software
--Microsoft Office Suite
--Microsoft Applications
to
--Microsoft Excel Spreadsheet Software
--Microsoft Office Suite
--Visual Basic Programming
Netminder
Senior Admin
http://www.experts-exchange.com/R_23308.html
Author: itjockey replied at 2024-09-01 21:54:58
i am not good in Code...
Thanks
Thanks
Assisted Solution
Expert: David Johnson, CD, MVP replied at 2024-09-01 21:18:33
250 points EXCELLENT
Dim GoogleXMLstream As MSXML2.DOMDocument compile error
Expert: ModeIT replied at 2024-09-01 19:55:50
itjockey,
A message has been sent to some additional experts asking them to review your question. We will check back again to see if you are getting the help you need.
This request included Experts from Microsoft Excel Spreadsheet Software, Microsoft Office Suite, Microsoft Applications.
Please do not respond to this comment; we will be monitoring your question for activity from the Experts.
Thank you for using Experts Exchange,
ModeIT
Community Support Moderator
http://www.experts-exchange.com/R_23308.html
A message has been sent to some additional experts asking them to review your question. We will check back again to see if you are getting the help you need.
This request included Experts from Microsoft Excel Spreadsheet Software, Microsoft Office Suite, Microsoft Applications.
Please do not respond to this comment; we will be monitoring your question for activity from the Experts.
Thank you for using Experts Exchange,
ModeIT
Community Support Moderator
http://www.experts-exchange.com/R_23308.html
Author: itjockey replied at 2024-08-02 08:48:51
I had used Web query & it is updating in 1 min but thing is that if i have to add some stock i have to log in to my Gmail account & manually have to add that ticker & again i have to done whole process to get 1 min refresh data.
Thanks
Thanks
Expert: duncanb7 replied at 2024-07-31 11:09:00
1 min is ok that querytable will fit you need exactly, please rewrite your code for querytable with 1 min refresh , your existing code is not good for refresh except running to hang
the whole Excel VBA time and system CPU time. Querytable with 1 min refresh won't
waste system CPU to do multi-tasking
Hope, you know what I said, talk you tommorw if need, I am leaving at building door now with my Izone.
the whole Excel VBA time and system CPU time. Querytable with 1 min refresh won't
waste system CPU to do multi-tasking
Hope, you know what I said, talk you tommorw if need, I am leaving at building door now with my Izone.
Author: itjockey replied at 2024-07-31 10:50:03
1 min ok I don't mind
Thanks
Thanks
Expert: duncanb7 replied at 2024-07-31 10:39:30
Please check what accuarcy you want first, on Excel VBA is least at at 1 minute that
is what you want ? how about in 1 hour or 1 day accuracy ? Hope you get my comment.
And state it clearly what you want to this thread, and try to make a test by yourself
from people suggestion first.
I am leaving office, talk you tomorrow if need
Duncan
is what you want ? how about in 1 hour or 1 day accuracy ? Hope you get my comment.
And state it clearly what you want to this thread, and try to make a test by yourself
from people suggestion first.
I am leaving office, talk you tomorrow if need
Duncan
Author: itjockey replied at 2024-07-31 10:33:41
But I want data to extract from Google only.
Author: itjockey replied at 2024-07-31 10:32:58
It is up to you how narrow down you can refresh time. I am happy with any duration.
Thanks
Thanks
Expert: duncanb7 replied at 2024-07-31 09:48:40
I am used to do it on real time on Excel VBA using querytable that accruacy is just 1minute only. so I switch to php and ajax
And Now I am using php curl and ajax javascript to do my daily stock data in real time in
whatever accuracy for data extraction time you want ( less than 3 seconds)
What accuracy for data extraction in real time, you want, 1sec, 3 sec, 15 sec, 1 minute for every data extraction before I decide to write some code for you, and less 1 minute is hard on Excel VBA
And Now I am using php curl and ajax javascript to do my daily stock data in real time in
whatever accuracy for data extraction time you want ( less than 3 seconds)
What accuracy for data extraction in real time, you want, 1sec, 3 sec, 15 sec, 1 minute for every data extraction before I decide to write some code for you, and less 1 minute is hard on Excel VBA
Author: itjockey replied at 2024-07-31 09:42:16
Mr.duncanb7,
Will you change some thing in my posted code to get real time data or one button to refresh the data.
Thanks
Will you change some thing in my posted code to get real time data or one button to refresh the data.
Thanks
Accepted Solution
Expert: duncanb7 replied at 2024-07-31 08:04:44
250 points EXCELLENT
Did you use Querytable table refresh before on Excel for real time ?
it might be faster or easier I guess
Now I am using that for yahoo refresh stock data, alternative using php coding
if you like, please google it for "Querytable refresh on Excel VBA, Yahoo stock data"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://yoursite.com?&Symbol=" & symbol, _
Destination:=Range("aa2"))
' .Name = "Quote: " & symb
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
it might be faster or easier I guess
Now I am using that for yahoo refresh stock data, alternative using php coding
if you like, please google it for "Querytable refresh on Excel VBA, Yahoo stock data"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://yoursite.com?&Symbol=" & symbol, _
Destination:=Range("aa2"))
' .Name = "Quote: " & symb
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
