1 2 3 4 This allows us to use guzzle from our file. 1 2 3 4 5 6 7 CREATE TABLE symbols ( id INT ( 6 ) UNSIGNED AUTOINCREMENT PRIMARY KEY, symbol VARCHAR ( 30 ) NOT NULL, ask DOUBLE, bid DOUBLE, open DOUBLE ) Next create an indexer.php file. What this file does is to query the yahoo finance api and then save the results to a csv file. Note that we can only query up to 200 symbols per request.
I was using the below saved data query in Microsoft Office Excel 2011 for Mac to download stock prices into a spreadsheet. This was indeed working fine until Friday April 28 when suddently it stopped working.
So we’ll have to work based on that on our code. The first thing that the code below does is to query the number of symbols currently in the database. And then we calculate how many times we need to loop in order to update all the symbols. We also need to declare the file path of the csv file in which will save all the results from the API. And initialize it by setting its value to an empty string.
Then we declare the format sabo. Which means symbol, ask, bid and open. Next we create a for loop that will keep on executing until the value of $x reaches the total loop times that we got from dividing the total number of symbols by the API limit.
Inside the loop we calculate the offset value by multiplying the current value of $x by the API limit. After that, we select the symbols that we need based on that.
Then we loop through the results, specifically the symbol and then put them in an array. After looping through all the results, we convert the array into a comma separated list. This allows us to use this value for querying the API. Once we get the result back, we just save it to the csv file using fileputcontents.
Yahoo Finance API provides a simple way to download stock quotes. The service returns stock data in a CSV. This blog explain how to get stock data from Yahoo Finance using Excel & VBA.
If you’re interested in a custom solution for your Excel file, please for a free quote. In this example, we want to obtain the following Stock info of Google, Facebook and Microsoft. Name of company.
Last trade date. Last trade (price only). Dividend Yield. P/E Ratio Pre-req Make sure Microsoft WinHTTP Services are enable.
To do so, open Visual Basic Editor of your xlsm file. Click Tools References. And select Microsoft WinHTTP Services. Demo You can also.
Development Process – Step 1 Range A2 to be taken the head cell. Type “GOOG” in range A3, “FB” in A4 and “MSFT” in range A5. Type “n” in range B3, “d1” in range C3, “l1” in D3, “y” in E3 and “r” in F3. A sample shown below. Step 2 Open Microsoft Visual Basic for Applications on your Excel.
Copy the code and paste it on your sheet’s VBA area. Double click to select all.
Option Explicit ' Example: Requesting Stock info of GOOG, FB and MSFT. ' ' In this example, we want to obtain the following Stock info ' - Name of company ' - Last trade date ' - Last trade (price only) ' - Dividend Yield ' - P/E Ratio ' ' Range A2 to be taken the head cell. Type 'GOOG' in range A3, ' 'FB' in A4 and 'MSFT' in range A5. Type 'n' in range B3, 'd1' ' in range C3, 'l1' in D3, 'y' in E3 and 'r' in F3. ' ' We use the following URL for getting Stock Quote from Yahoo ' URL: ' ' The CSV file data looks like the following ' ' 'FB','Facebook, Inc.' ,'9/4/2013',41.7665,N/A,189.46 ' 'MSFT','Microsoft Corpora','9/4/2013',31.24,2.89,12.36 ' ' The columns in CSV file is separated by comma.
Split function ' is used to split the data on each line of CSV file at every ' occurrence of comma. However, some commas in the CSV file are ' part of a string, not separating the columns. For example ' consider the following line, ' 'FB','Facebook, Inc.' ,'9/4/2013',41.7665,N/A,189.46 ' The second comma is not a column separator.
But it's part ' of the company's name 'Facebook, Inc.' This did not quite work on my Excel for Mac 2011. I adapted it to a version that does appear to be working, which required using QueryTables in place of the HTTP Get call, which evidently is not supported by Mac Excel 2011. Something changed recently and this code no longer works.
When I ran it in the debugger it appears that Http.responsetext in Sub PrintCSV no longer contains newline characters – so the Dim Lines As Variant: Lines = Split(Resp, vbNewLine) no longer produces an array of lines one per stock quote. I am no sure why this is – I looked at the data yahoo is returning and each line is delimited with character 0xa which is the unix style line terminator.
I have been hacking away on an alternate line parser using the raw data in Http.responsebody but running into a few snags since I am unfamiliar with visual basic programming. Not sure what happened – either yahoo changed their data format or the http response parsing code changed somehow. Here is some less than elegant code that seems to work – This replaces your version of PrintCSV. The only thing I don’t like about it is that I allocate a fixed size array of 300 strings to hold the results of the line parse. I tried experimenting with more dynamic allocation methods but did not get too far.
‘ Download the CSV file and Print it in the cells Sub PrintCSV(URL As String, head As Range) ‘ Get the CSV file from Yahoo! Here is what I have so far, completely lost on next steps Do we use Yahoo API to get the xml then parse it into Excel? Dear, Thanks a million for the code. I’ve been using it for a while. I have some minor issues and wish if you can guide me to fix them within your code.
The issues are: – When I add new column before the “Head” Column, or new row above the “Head” row, the code doesn’t run, The error message is “Head is not found!”. Is it possible to make the head cell dynamic to be anywhere in the sheet? – When I add a column in the middle without any yahoo tag to use it for my own calculations, the data gets updated in the columns before the empty one, but the data in the columns after the empty one doesn’t get updated.
The same with the rows as well. Any empty row in the middle (without the company’s ticker) will make the data gets updated in the rows above only. Please let me know if this can be fixed. Kind regards, Waleed. Hey thanks for updating the code – much appreciated! It’s been very helpful to me these last couple of years and I was so glad I didn’t have to search for replacement solution.
I made a couple of changes in the PrintCSV Sub one that speeds up the execution, namely turning off the screen updating while it writes the CSV and the other giving a status bar update message just to let you know what’s going on.