VBA Function To Pull Stock Data From Website
Feb 17, 2012
I recently created a spreadsheet to calculate the value of certain stocks. I chose to do the s&p 500. It can take a while to manually get the info I need, so I looked to videos on vba writing and different solutions. I do not want to use web queries. Rather, I'd like to use a simple formula that pulls the necessary "single" item from a webpage and puts in a cell, then I will simply copy and paste that cell for all stocks.
from my online endeavors and inquiries, I put together this POS:
Function vi_Price(ByVal Symbol As String)
Dim IE As New InternetExplorer
IE.navigate "[URL]...
Do
DoEvents
[Code]...
Using Google Chrome, I tried to find the "element" that I need. In this case, the last close price at the top of the webpage. It's hard to distinguish which it is. If I can just get the code to work for the price, I think I can modify it for the other 3 items I need.
View 2 Replies
ADVERTISEMENT
Jul 24, 2014
Looking for a formula to accomplish the following:
I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".
View 6 Replies
View Related
Apr 4, 2008
Here is what i'm working with:
http://img90.imageshack.us/my.php?im...uestionas8.jpg
what i want it to do is pull out the stuff in column C and put it in column F. But i don't want it to duplicate it at all. So for instance in this case, submarket 1 would be cary-morrisville-apex / submarket 2: northwest wake / submarket 3: north wake etc.
would this involve a VLOOKUP function but an if then function as well? I'm not really sure how to go about it.
View 14 Replies
View Related
Oct 6, 2008
I've been using the DSUM function to pull in data from another worksheet.
In brief, the problem is that when I Cut & Paste the formula from C7 to C8, I want the Column reference to change but not the Row reference.
If I copy across the Columns then the result is OK – e.g. The Formula will change from…
#=DSUM([HBAP_SHUTDOWNS_2008.xls]NWM_SuccessfulShutdownsByDepartment!$A$4:$E$2924,"Total",'Calculations Page'!A$17:A$18)#
To…
#=DSUM([HBAP_SHUTDOWNS_2008.xls]NWM_SuccessfulShutdownsByDepartment!$A$4:$E$2924,"Total",'Calculations Page'!B$17:B$18)#.........................
View 4 Replies
View Related
Aug 7, 2009
I have limited experience at writing functions and I can't seem to get this one to work, in fact I think I am way off. I wan to calculate weeks of stock on hand assuming an opening value of stock and assumed sales levels.
For example 5000 opening stock on 1 Jan and sales of 1000 in Jan, 2000 in Feb and 4000 in March would be calculated as:
52/12*2+(5000-2000-1000)/4000*52/12 = 10.8 weeks
I have written an if formula to calculate this however it is cumbersome has limitations due to the number of if's that can be entered.
View 9 Replies
View Related
Aug 7, 2012
What is the easiest way to have a cell update with real time stock quotes for a particular stock?
View 4 Replies
View Related
Jan 8, 2014
I have created a stock sheet on Sheet1, i have say 100 items and each item has a min and max stock order. Once the item hits a min low, the last cell (O) will display an order needs to be placed.
I was wondering if there way a way that on sheet2, it can calculate all the data on sheet1 and if there is stock that needs to be ordered, it will appear on sheet2?
This way i can just print sheet2 and send it to the supplier without having the entire 100 items displayed -if it does not need to be ordered?
View 7 Replies
View Related
Nov 3, 2008
here is what i have but i want it to adapt it you use any company by using a list of companys in a worksheet "StockData". So the company names start in "A2" and go down the column. So i want to run the download then for the modual to go to the next company in the list without having to have a sub() for each company....
View 9 Replies
View Related
Aug 2, 2012
I'm working on getting company data from yahoo into excel. I can do this manually going to Data > From web etc using site: [URL] ... for example then just import from there.
I recorded this macro:
Sub OriginalImport()
'
' Macro2 Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
[Code] .......
This works fine. But i want to be able to manually select the stock code, start month, date and year and end month, date and year through excel (this is already setup easy) and have this update the URL that is pasted into here: [URL] ....
In cell A1 i have used this formula: [URL] ......
This spits out something like this: [URL] .....
That will automatically update the correct URL name to enter into the correct section of the macro. But here's where i hit a snag I've tried:
With ActiveSheet.QueryTables.Add(Connection:= _
= Range("A1") _
, Destination:=Range("$A$2"))
But it doesn't like the =Range("A1") bit. Once i solve how to get this URL into the connection web-search space then i can control what my macro searches for. What do i have to do to the
= Range("A1") _
bit to make this macro work??
View 2 Replies
View Related
Mar 9, 2008
I have stock data values being updated on my sheet every 10 seconds in one Cell (E5).
I am looking to indicate a change in that cell value by changing cell color (or could be any easily programmable indicator). I want to change the cell color on first change of value but not subsequent refreshes. Stock data is numeric in string form. colors used not important as long as there is a visible change.
I Have tried playing with sheet change events but no progress as data is not manually entered.
View 3 Replies
View Related
Jun 20, 2014
I have a tedious task of copy/paste from our website. have a look at below sample data. I have around 1500 rows of data like this.
Sheet1
A
B
[Code]....
Column A has input data which i need to copy and paste in a web form. Column B will have results scrapped form webpage[ URL]. This webpage will need login details
If a code can be written which take value form column A of spreadsheet and paste in webpage like shown in above image and hit search button. Next webpage will be loaded with number of results like shown in below. Take the number of results and paste in column B of spreadsheet.
View 1 Replies
View Related
Feb 14, 2008
I have a vlookup formula which can refer to any one of several sheets labelled by month.
I need to be able to show in a different cell which sheet (month) the formula refers to.
In other words - displaying part of a formula in a different cell.
View 3 Replies
View Related
Mar 16, 2014
Below are the weekly closing prices for a stock market index. I'm looking to use conditional formatting to highlight in green "buy" signals and in red "sell" signals.
Buy Signal: If the index rises 4% above any previous low point.
Sell Signal: If the index falls 4% from any previous high point.
DateAdj Close
3/10/201472.16
3/3/201473.55
2/24/201472.83
2/18/201471.92
2/10/201471.43
[code].....
In the above example a Buy Signal is generated on 1/13/2014 because the market closes 4% above the low of 68.24 on 12/9/2013.
[URL]
View 4 Replies
View Related
Sep 4, 2007
I look at stock charts that allow an overlay of "zigzag patterns." This zigzag links price extremes with a line. The price extremes are defined according to "wave size" which is described as follows:
"Wave Size: The Most Important Setting... and the thing you will want to tinker with -- is Wave Size. Wave Size is a number that defines a high or low price extreme, by stating the number of surrounding bars on either side that must not be higher or lower than the candidate extreme. Another way to think of it is: Wave Size + 1 is the minimum distance between extremes of the same high or low type."
Here is an example of what a stock chart with these zigzags drawn in looks like. [Click on the image to zoom in and see it better.]
[URL]
The black diagonal line is built using a "wave size = 5" using the above definition. And the green one is, "wave size = 20"
I am trying to build this into a spreadsheet so I can play with the data. If I have four columns: (a:open)/(b:high)/(c:low)/(d:close) I cannot figure out how to build the columns after to do this zigzag. I understand what the line is, but it just doesn't make sense to me how to derive it from the data. how to get a column n that has the value of the zigzag (with wave size = 5 for example) I
View 6 Replies
View Related
Jul 30, 2013
I am trying to extract data from a website:
[URL] .....
I looked at the source code of the website and realized that if you notice (above) that the variables listed in the link (i.e year, month, day) are exactly what i need to change in order to get the data for a specific date. how can I accomplish this using VBA. so say I have in on an excel sheet year in column A, month in column B, and days in column C (time interval is constant so we don't have to worry about stime and etime). and i run the macro and it loops through each row taking year,month,day for all rows and saving the data as .csv or xls files?
View 1 Replies
View Related
Jul 31, 2013
retrieving data from financial website databases like yahoofinance.com and bloomberg.com. I'm trying to make an automatic stock analysis model to read from the website database and retrieve the data into excel sheets. For example, when opening the excel model the user gets a popup to enter the stock ticker, the user enters the ticker and gets a set of data. Is this do-able in excel?
View 2 Replies
View Related
Jun 20, 2014
I have list of url in a column. I want to fetch data from all the links and store it in a excel sheet.
I have written code, the code fetches only 1 links data at a time. I want to loop that vba macro and fetch all the data at a time.
View 4 Replies
View Related
Feb 8, 2008
I have written a macro to pull in data from a website each day. However the macro falls over because the end of the url changes slightly each day as the website provider updates the information. Unfortunately there does not appear to be a logical pattern to the number change in the url and so I don’t think I can pre-empt what the change will be. The path through the website remains the same, but the url changes as a new csv document replaces the old one. Is there any way of writing the macro so that it opens/picks up the current url rather than a fixed url?
View 9 Replies
View Related
Aug 24, 2008
I went through the thread http://www.mrexcel.com/forum/showthread.php?t=302438. I have similar problem of pulling the data from http://www.nseindia.com/ site. Could you please help me?
The navigation to the page required is as below.
http://www.nseindia.com/ > Equity > Historical Data > Security-wise data >
eg. NSE Symbol: RPL, Series: ALL, From date: 01-01-2008, To date: 01-05-2008.
After clicking on the 'Get Results' button it navigates to the page with tables. At the end it says "Download file in csv format". upon clicking this a csv file opens. (http://www.nseindia.com/content/equi...-2008RPLXN.csv)
I need this page to be opened in Excel sheet. How should I go ahead?
(Basically I need historical stock data csv files of various stocks)
Even though a simple web query opens the page, it do not work if the parameters (stock name, date) are changed.
I found that the last resulting page in csv is using a "REFERRAL URL". Could this be a problem? How to get around?
I know little bit of VBA and can further work to pass variables.
View 9 Replies
View Related
Jan 8, 2010
Is it possible to extract data from a website into an excell workbook using data from the workbook?
I have rough workbook that i gather data from 3 different websites to calculate a rough market value for DVDs that I buy and sell. I am good with excel but have no idea about importing data into it.
Currently I copy and paste from the sites on an as needed basis but it is a slow slow task when I have several dozen or more to do per hour.
View 9 Replies
View Related
Jun 3, 2003
I wish to surf to a website and retrieve the bottom entry in a column The column updates dynamically with a new number added to the bottom on a daily basis.
Here is the website URL: http://global-view.com/fxhist.TXT.
The column I am interested in is the second column from the left under the History section. The column contains close prices for the currency symbol EUR/$.
View 9 Replies
View Related
Apr 19, 2006
I have managed to manipulate a website by entering some dates into the input boxes then go to another page with the information I need. I am wondering if anyone knows a macro I can use to copy the data directly to a new worksheet?
my code is below ...
View 9 Replies
View Related
Mar 8, 2013
I've only recently ventured into the VBA scripting and am finding it quite exciting. After doing some trivial stuff, I'm trying to work with websites. Currently, I'm trying to pull data off of a website which poses a form which has to be filled first. I've been successful in filling the form and clicking submit, through the code, but the site opens the results in a separate tab in IE. I am unable to make it go to that tab and copy-paste data from there.
Secondly, instead of just blindly copy pasting, it would be better if the data is pasted in a more readable format (as on the website). The following code is plainly copying and pasting the data as-is and hence rendering it unreadable:
VB:
.ExecWB 17, 0
.ExecWB 12, 2
.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False
View 1 Replies
View Related
Jun 21, 2013
I am an excel novice trying to create a list of local churches. There are several online lists that I am taking the data from but, being a novice, I am entering the data manually. How to set up a method to bring the data into excel and order it where I want it.
I am attaching the spreadsheet.
These are the websites I am taking my data from: [URL]
View 3 Replies
View Related
Jun 3, 2003
From inside Excel, I wish to surf to a website and retrieve the bottom entry in a column The column updates dynamically with a new number added to the bottom on a daily basis.
Here is the website [URL].......
The column I am interested in is the second column from the left under the History section. The column contains close prices for the currency symbol EUR/$.
View 9 Replies
View Related
Jun 28, 2012
I need to get data from a website that requires to log-in, so i set this Vba, and i get an error "object dosent support this property or method"?
Sub GetTable()
Dim IEApp As Object
Dim IEDoc As Object
Dim IETable As Object
Dim clip As DataObject
Set IEApp = New InternetExplorer
IEApp.Visible = True
[code]....
View 1 Replies
View Related
Dec 22, 2012
I have a user form that will navigate to a website where the user can find the latitude/longitude coordinates of a desired point.
What I would then like to do is "pull" the latitude/longitude into cells B1 and B2 respectively.
I have attached the workbook.
[URL]
View 9 Replies
View Related
Mar 11, 2014
Doing all 47 pages at once may cause their site to block your IP. Though usually only temporarily - a few minutes to a few days. You could do it in sessions by changing "For i = 1 To 47" to the appropriate page numbers. After each session be sure to rename the "Stats" sheet as the code starts by deleting and re-creating it. After all sessions combine the various "Stat" sheets.
Doing all 47 at once will take a few minutes to complete and you won't see anything happening. The cursor will only spin occasionally. You wil get "Query complete" when it finishes.
Code:
Sub QueryWeb()
Dim i As Integer
Dim firstRow As Integer
Dim lastRow As Integer
Dim nextRow As Integer
Dim URLstart As String
Dim URLend As String
[Code]....
This code works like charm. Is is possible to develop a code like this which open each each name from this page- [URL]....
AND
copy details like Attorney Name, email, phone and fax nos. and paste the same into excel rows.
View 9 Replies
View Related
Dec 22, 2008
The code below should copy website data to XL 2003
However an error occurs at linemarked (**)
Error is:
Object doesn't support this property or method (Error 438)
adn the online help is just UNHELPFUL.
Sub CopyInternetDoc()
Dim IntApp As Object
Set IntApp = CreateObject("InternetExplorer.Application")
With IntApp
' Change file name to suit
.Visible = True
.Navigate "Intranet website"
.ActiveDocument.Select **
.Selection.Copy
End With
ActiveSheet.Cells.Select
Selection.ClearContents
ActiveSheet.range("A1").Select
ActiveSheet.Paste
IntApp.Quit
Set IntApp = Nothing
End Sub
View 9 Replies
View Related
Feb 21, 2010
I have a bunch of lists I would like to copy from a website. I'm trying to copy them into excel using an external data query but the information I'm trying to grab doesn't export. I tried selecting various tables within the webpage.
View 9 Replies
View Related
