Data Source from Web (or .csv file d/l from web)

[thereuare]

This is a webpage which opens a CSV file and displays the data in a table on the webpage (page is from Yahoo Finance, stock symbol MSFT):

http://table.finance.yahoo.com/table.csv?a=3&b=27&c=2002&d=6&e=28&f=2002&s=msft&y=0&g=d&ignore=.csv

I'd like to be able to prompt the user for a stock symbol using a Dialog Box, and then have my macro read the corresponding CSV file. I already have the program able to read a CSV file if it's on my hard drive, but how can i use the data coming from a web source (instead of a file on my hard drive)?

I'm not married to using the above method/website, just thought i'd add it to aid anyone that could help.

Thanks for any help OR pointing me in the right direction!

 

[thereuare]

PaddyD,

Well, i've been staring and playing with the other link you gave me for the last 2 hours, and i don't think i'm any closer to solving my situation.

I think i don't fully understand the other person's code, as far as how the .txt file is modified. I understand the process (although not all the code) of what s/he did, i just don't see how where the .txt file takes on the modification.

Here's what i have so far (most taken from the other page):

Sub Modify_IQY_File()
SYMBOL = InputBox("Ticker??")
Workbooks.OpenText Filename:= _
"C:locationYahoo.txt"
ActiveWorkbook.SaveAs Filename:= _
"C:locationYahoo.iqy", FileFormat:=xlText _
, CreateBackup:=False
ActiveWindow.Close
Run_Query

End Sub
Sub Run_Query()
Sheets("Sheet2").Select
Columns("A:Q").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:locationYahoo.iqy", Destination _
:=Range("A1"))
.Name = "RFP Query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub

Where Yahoo.txt is:
WEB
1
http://table.finance.yahoo.com/d?a=3&b=29&c=02&d=6&e=30&f=02&g=d&s=MSFT

Selection=AllTables
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False

I suppose all would work fine if i could have the SYMBOL (from the input box) get substitued for MSFT above, so it should look something like this:

WEB
1
http://table.finance.yahoo.com/d?a=3&b=29&c=02&d=6&e=30&f=02&g=d&s=SYMBOL

Where SYMBOL is the stock symbol inputted.

A few hours later, and i'm still stumped?!?!

 

[PaddyD]

thereuare,

Unfortunately, my VB's up there with yours, so what follows will be necessarily schematic - we can hope one of the VBers out there can fill in the gaps...

We need a source spreadsheet (or something) to take the users input. From your detective work on the pathname, it's not going to be enought to have just the MSFT element variable - we'll need user input for each of the date options as well.

Book7
	A	B	C	D
1
2	Get Stock Quotes:
3
4	Start Year?
5	Start month?
6	Start Day?
7	End Year?
8	End Month?
9	End Day?
10
11	Which Stock?
12
13
...

Then a macro button that would run the code that would create the appropriately formatted url for the query, save the file & run the query...

(Obviously, you'd change the input area to have simply start date & end date, then extract separate day / month / year values from that in the code - I just though I'd be explicit.
You could also probably get away with not knowing what the y & g forms take as input as long as it doesn't effect the data you're getting back, just format theend of the url as in you example).


So here's the appeal:

can anyone wite a macro that will generate a text file of the format:

WEB
1
http://table.finance.yahoo.com/table.csv?a=3&b=29&c=2002&d=6&e=30&f=2002&s=msft&y=0&g=d&ignore=.csv


but where the values for a= , b= etc are generate on the basis of user inputs as described above?

I've said it before - I really am going to have to learn some VB!

Paddy

 

[thereuare]

I agree on "the bottom line of what we're looking for", i just figured if i could figure out how to make the stock symbol use a variable, i could just apply the same methodology for the month, day, year, etc.

So can anybody out there lead us in the right direction to have code create a text file using user input?

Thanks in advance.

 

[PaddyD]

thereuare,

I we don't get a response, I'll use this opportunity to force myself to start to learn some VB & try & get you an answer tomorrow.

Don't let that stop anyone else, though

Paddy