Hi, I would like someone who can input the historical price and volume data of the S&P 500 stocks into Google Correlate, which will then give me the search terms whose search frequency best matches that data. The results should be sent to me in 1 clearly labelled and set out Excel file. I have detailed instructions on how to do this below, but if you have a quicker way of doing it feel free to use it.
Step 1. Select the first company from the S&P500 ( list found here:http://en.wikipedia.org/wiki/List_of_S%26P_500_companies)
Step 2. Enter the company's ticker into Yahoo Finance (http://finance.yahoo.com/)
Step 3. On the lefthand bar, select historical prices. Then select Weekly. Select start date 01 Feb 2005.
Step 4. Click Download to Spreadsheet at the bottom of the page
Step 5. The dates will be descending order e.g. from 26/08/2013 to 27/01/2005 (DAY/MONTH/YEAR). Select the column of dates, then use CTRL + 1 to access the format page. Select date in the format box, then select locale English (US). Then choose the date format 3/14/2001 (Month/Day/Year). All dates in the date column must be transformed into MONTH/DAY/YEAR format.
Step 6. Create 2 new excel files in the .csv format. In the first file, copy the original date column and paste it in as the first column. Copy the column of CLOSE data in as the second column. There should now be two columns, with no headers and with numbers starting in the first row. Save this as (ticker name)Close.csv- e.g. AAPLCLOSE.csv. The second file will be the same, except the second column will be the VOLUME column from the original excel file. Save this as tickernameVOLUME.csv.
Step 7. Got google correlate at http://www.google.com/trends/correlate
Step 8. Click Enter your own data. Click Choose your file. Select the .csv file of CLOSE data you have just made (e.g. AAPLCLOSE). Give the Time Series a name, say Apple. Click Search Correlations.
Step 9. A new screen with terms and correlations should appear, looking something like the picture in this website: http://searchengineland.com/google-correlate-more-search-data-to-mine-78560. Click show more twice. Copy the correlations and the search terms into an excel file.
Step 10. Now click the Shift series down arrow so it shows -1. Click show more twice again, then copy and paste the correlations and search terms again.
Click the Shift series down arrow again so it shows -2. Click show more twice again, then copy and paste the correlations and search terms again.
Step 11. Repeat steps 8 through 10 for the VOLUME file for the same ticker (e.g. AAPLVOLUME)
Step 12. Repeat Steps 1 through 11 for all the other stocks in the S & P 500.
Step 13. Send all results to me in 1 clearly labelled excel file.