Find and amend fundamental and macro data points for US and global stocks and countries

Amend an excel file with a series of additional fundamental and economic macro data points.

Good knowledge of VLOOKUP, INDEX and MATCH functions in Excel, or equivalent data manipulation tool, may be helpful. VBA or SQL would help since the data volume is rather large. Samples of two months worth of data should be provided by short-listed bidders to ensure that the task is understood. No help may be provided once the project is awarded. Data errors or deviations from the example attached will need to be corrected by the winning bidder except for any additional columns included in the source data of this project. Numbers need to be numbers and text needs to be text data types. Trailing or leading spaces should not occur in the final result. Any macros need to be removed form the resulting XLS files.

Note the recommended data source for the additional data is Quandl ([url removed, login to view]), at least for the US. Quandl codes are provided in the instructions of work to identify the expected data elements to be used. Other data sources may be used, but only if the data source permits redistribution and personal/private use of data and if the data points are equal to the data found on Quandl. The requirement is to use at least quarterly data points applicable for the date. The actual daily data point may be used optionally if the data source does provide that data.

Critically note, that I currently have no good idea where to collect the data for non-US tickers. Please do not ask or bid if you are unsure where to collect such data. In the even that no bid can be awarded the project will be re issued to just cover US stocks.

Some data sources may have restrictions regarding the number and volume of data query. Please consider these when estimating the proposed completion time. Also, some data sources do not offer the full length as in the attached file, this is expected but the earliest starting point needs to be indicated.

Instructions of work:

1. Download the attached Excel file

2. For each “Date” and “Ticker” create a new column inserting the data for that date and ticker for the data attribute as indicated below:

2.1 Column: “NetIncome” data point as in “SEC/<TICKER>_NETINCOMELOSS_Q”

2.2 Column: “Revenue” data point as in “SEC/<TICKER>_SALESREVENUENET_Q”

2.3 Column: “TotalAssets” data point as in “SEC/<TICKER>_ASSETS_Q”

2.4 Column: “PretaxIncome” data point as in “SEC/<TICKER>_INCOMELOSSFROMCONTINUINGOPERATIONSBEFOREINCOMETAXESEXTRAORDINARYITEMSNONCONTROLLINGINTEREST_Q”

2.5 Column: “EBIT” data point as in “SEC/<TICKER>_OPERATINGINCOMELOSS_Q”

2.6 Column: “ShareholdersEquity” data point as in "SEC/<TICKER>_STOCKHOLDERSEQUITY_Q”

2.7 Column: “SharesOutstanding” data point as in "SEC/<TICKER>_WEIGHTEDAVERAGENUMBEROFDILUTEDSHARESOUTSTANDING_Q”

2.8 Column: “EarningsPerShareDil” data point as in "SEC/<TICKER>_EARNINGSPERSHAREDILUTED_Q"

2.9 Column: “Dividends” data point as in "SEC/DIV_<TICKER>“

2.10 Column: “MarketCap” data point as in Price from “GOOG/<EXCHANGE>_<TICKER>” times “SharesOutstanding”

2.11 Column: “Volumen” data point as in Volume “GOOG/<EXCHANGE>_<TICKER>” for the covered frequency (not that a monthly frequency needs to sum the monthly volume.)

3. For each “Date” and “Country create a new column inserting the data for that date and country for the data attribute as indicated below, where country code may need to be derived before Quandl code can be used:

3.1 Column: “10yBondYield” data point as in "UNDATA/IFS_YLD_<COUNTRY_CODE>“

3.2 Column: “GDPChange” data point as in “ODA/<COUNTRY_CODE>_NGDP_RPCH”

3.3 Column: “DeptRatio” data point as in “WGFD/<COUNTRY_CODE>_GFDD_DM_10”

4. Data cleansing: For any data combination that does not yield a result do not omit the result. Insert “NA” when there is no data to be found.

5. Make a CSV file available to complete the project

The following are option tasks:

A) Amend each “Date” and “Ticker” with the trailing 20 day momentum indicator as of the date per “Date” column

Accounting Data Entry Data Processing Investment Research Web Scraping

Project ID: #7954378

About the project