Homework 1-2

The purpose of this assignment is to get experience importing data from the web. You will be importing historical stock data, performing a some computations and applying conditional formatting to help display your analyses. We'll be using Quandl, a source for economic and financial data.

Task 1

To work with Quandl, you will need an API key. Sign up for one on Quandl

Task 2

Create a new spreadsheet. Choose 10 US-based stock symbols of interest to you. You could choose the top 10 companies based on their market capitalization (market cap). You could look at companies with the best stock performance in 2016. The choice is up to you! Place the stock symbols in the first row of your spreadsheet.

Task 3

On the next row, build your query URLs. The base web query looks like, https://www.quandl.com/api/v3/datasets/WIKI/AAPL/data.xml. Here, WIKI is their database of 3,000 US stocks and AAPL is the stock symbol for Apple that we are looking at. A fully formed URL will also require several parameter and value pairs.

Write a formula to build the base URL and replace AAPL with the stock symbols you chose using string concatenation. Use a cell reference to refer to the appropriate stock symbols. You should be able to use a fill to automatically build the URLs for all your stock symbols.

Additionally, the URL requires a few parameters that will be common to all stock symbol URLs. For each parameter and value we list below, place them in their own cell to the left of your first stock symbol column. Since we'll be referencing these cells with formulas, we'll be able to reload the data automatically if we want to change the values.

The first required parameter is 'api_key', the value should be the weird string you got when you signed up for one on Quandl.

Try typing one of the URLs you built in a web browser as it exists now. You should see a huge page of XML that takes about 30 seconds to load. If you get a page that only loads a short snippet saying you 'submitted an incorrect quandl code', you will need to choose a different symbol.

For example, the URL for our AAPL example should look like https://www.quandl.com/api/v3/datasets/WIKI/AAPL/data.xml?api_key=BYz_ntRbuzgUZfdaD9Ek, except the value for your api_key should be different.

This returns all the historical data for the stock symbol, but to keep things reasonable, let's look at only the last year's worth of data. This requires a 'start_date' parameter that takes a date value formatted as YYYY-MM-DD.

If you try the URL in a web browser now, it should load a lot faster.

The last piece is that we want only want the CLOSE price, which is the price the stock last sold before the stock exchanges closed at the end of the day. If you look at the data in the web browser, you'll see an xml element labeled "column-names", which are the column labels. We want the index, which means that 0 is the first index and column. In this case, the Date column. The Close price is the 5th column, which means its index is 4. Add the parameter, 'column_index', with a value of 4 to your spreadsheet.

Task 4

Use IMPORTXML to load the stock data in each stock's column below their URL cell. Formulate a suitable xpath_query to select the correct element's data to display.

Task 5

Use conditional formatting to show in green the stocks that increased from the day before, and red to show the stocks that decreased from the day before.

Task 6

We'll simulate the performance of an actual stock portfolio. Insert a row just below your symbols and put in a value from 1 to 10 in each column to show how many shares of each stock you 'own' (we all can dream!). In a column to the right of your last symbol, compute the total value of your portfolio for each day. Conditionally format the column like the others to show green when it increased and red when it decreased.

Task 7

On a separate sheet, please put down how many hours you worked on this assignment, people you worked with and whether you went to TA hours for help or clarification on this assignment.


Once you're done, share your file with cs0030handin@gmail.com by midnight, 2/1.

Make sure your submission has your name in the filename: FirstLast_HW1-2. “FirstLast” should be replaced with your first and last name or we will take off points. Make sure every task has been completed.